May 25, 2010 at 7:16 pm
Hi
I have a table
declare @table TABLE(
K int
, [type] int
, [value] int
)
INSERT INTO @table
SELECT 1 as K, 1 as [type], 100 as [value]
UNION ALL SELECT 2 as K, 1 as [type], 200 as [value]
UNION ALL SELECT 3 as K, 1 as [type], 300 as [value]
UNION ALL SELECT 4 as K, 2 as [type], 400 as [value]
UNION ALL SELECT 5 as K, 2 as [type], 500 as [value]
UNION ALL SELECT 6 as K, 3 as [type], 600 as [value]
UNION ALL SELECT 7 as K, 3 as [type], 700 as [value]
I want to get result:
K type sum
----- ------ ----
1 1 600
4 2 400
5 2 500
6 3 600
7 3 700
It group by if type=1, if type <> 1, don't group by:-D
Please help me to write SQL with performance
May 25, 2010 at 7:53 pm
Great job of posting the table DDL, sample data, and expected output! Makes me just want to jump in and help you out.
How does this work for you?
SELECT DISTINCT
K = min(K) OVER (PARTITION BY [type]),
[type],
sum = sum(value) OVER (PARTITION BY [type])
FROM @table
WHERE [type] = 1
UNION
SELECT K, [type], value
FROM @table
WHERE [type] <> 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 26, 2010 at 5:20 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply