Get the value by grouped by columns 1

[CrystalProgDev]

I have a table with the below values
ID COL1 COL2 COL3
1 AAA P1 50
2 AAA P1 50
3 AAA P1 50
4 BBB P1 150
5 BBB P1 150

I need output as below with another column 'Sum' as sum of distict values of COL3 grouped by COL1 and COL2, in this case 50+150

ID COL1 COL2 COL3 Sum
1 AAA P1 50 200
2 AAA P1 50 200
3 AAA P1 50 200
4 BBB P1 150 200
5 BBB P1 150 200


Can you please help me in provideing the solution?

Thank you in advance

 

[CrystalProgDev]

I figured out the solution. Thank you anyway...

 

[gbaughma]

Please post your solution, so that others may benefit from it.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[CrystalProgDev]

Sorry about the late response. the solution is to have sub query to get the sum(COL3) grouped by COL 2

SELECT SUM(COL3) AS Expr1
FROM (SELECT COL2, COL3 FROM Table T)
GROUP BY COL2
GROUP BY COL2)

Thank you