Sum Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sum Group By

Author

Topic

cvipin
Yak Posting Veteran

51 Posts

Posted - 2013-10-26 : 02:03:19

Let's say I have a table with 3 columns Col1, Col2 and Value .The data in these columns is as below

DECLARE @RptValues TABLE
(Col1 varchar(1), Col2 varchar(2), Amt decimal(18,2))

INSERT INTO @RptValues VALUES ('A','B',100)
INSERT INTO @RptValues VALUES ('B','C',200)
INSERT INTO @RptValues VALUES ('C','A',300)
INSERT INTO @RptValues VALUES ('C','D',400)
INSERT INTO @RptValues VALUES ('B','A',500)
INSERT INTO @RptValues VALUES ('A','C',500)

I want to group on Col1 and Col2 so that AB and BA as an example are treated as same. The exprected output is:

Col1 Col2 Value
A B 600
B C 200
C A 800
C D 400

OR

Col1 Col2 Value
B A 600
B C 200
A C 800
C D 400

Can you please help.

Thanks
Vipin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-26 : 02:51:55

[code]select Col1 = left(Col, 1), Col2 = right(Col, 1), Amt
from
(
select Col = case when Col1 < Col2 then Col1 + Col2 else Col2 + Col1 end, Amt = sum(Amt)
from @RptValues
group by case when Col1 < Col2 then Col1 + Col2 else Col2 + Col1 end
) r[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 04:51:21

[code]
SELECT COALESCE(t1.Col2,t.Col1) AS Col1,COALESCE(t1.Col1,t.Col2) AS Col2,SUM(Amt) AS Total
FROM Table t
OUTER APPLY (SELECT Col1,Col2
FROM @RptValues
WHERE Col1 = t.Col2
AND Col2 = t.Col1
AND Col1 > Col2
)t1
GROUP BY COALESCE(t1.Col2,t.Col1),COALESCE(t1.Col1,t.Col2)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cvipin
Yak Posting Veteran

51 Posts

Posted - 2013-10-26 : 10:32:56

Thanks khtan, your solution works great.
visakh16, your solution gives the same results as source table.

Thanks
Vipin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 15:18:14

Sorry tht was a typo

see this illustration


DECLARE @RptValues TABLE
(Col1 varchar(1), Col2 varchar(2), Amt decimal(18,2))


INSERT INTO @RptValues VALUES ('A','B',100)
INSERT INTO @RptValues VALUES ('B','C',200)
INSERT INTO @RptValues VALUES ('C','A',300)
INSERT INTO @RptValues VALUES ('C','D',400)
INSERT INTO @RptValues VALUES ('B','A',500)
INSERT INTO @RptValues VALUES ('A','C',500)

SELECT COALESCE(t1.Col1,t.Col1) AS Col1,COALESCE(t1.Col2,t.Col2) AS Col2,SUM(Amt) AS Total
FROM @RptValues t
OUTER APPLY (SELECT Col1,Col2
             FROM @RptValues
             WHERE Col1 = t.Col2
             AND Col2 = t.Col1
             AND Col1 > Col2
             )t1
GROUP BY COALESCE(t1.Col1,t.Col1),COALESCE(t1.Col2,t.Col2)
ORDER BY COALESCE(t1.Col1,t.Col1),COALESCE(t1.Col2,t.Col2)


output
---------------------------
Col1	Col2	Total
---------------------------
B	A	600.00
B	C	200.00
C	A	800.00
C	D	400.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources