SQL SUM and 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 2005 Forums

Transact-SQL (2005)

SQL SUM and Group By

Author

Topic

btisdabomb
Starting Member

2 Posts

Posted - 2007-09-18 : 09:51:56

I have the following query...

SELECT  mc.modelproductdetailid,     
mp.modelproductid,
mpd.subproductid,
SubProdPref.valuetext,
SUM(CASE WHEN timeperiod = 'Y0' THEN mc.Reimbursement END) AS Y0Reimbursement,
SUM(CASE WHEN timeperiod = 'Y1' THEN mc.Reimbursement END) AS Y1Reimbursement,       
SUM(CASE WHEN timeperiod = 'Y2' THEN mc.Reimbursement END) AS Y2Reimbursement,       
SUM(CASE WHEN timeperiod = 'Y3' THEN mc.Reimbursement END) AS Y3Reimbursement,
SUM(CASE WHEN timeperiod = 'Y0' THEN mc.OtherValue END) AS Y0OtherValue,  
SUM(CASE WHEN timeperiod = 'Y1' THEN mc.OtherValue END) AS Y1OtherValue,
SUM(CASE WHEN timeperiod = 'Y2' THEN mc.OtherValue END) AS Y2OtherValue,
SUM(CASE WHEN timeperiod = 'Y3' THEN mc.OtherValue END) AS Y3OtherValue,
msp.TotFinImpactY0,
msp.TotFinImpactY1, 
msp.TotFinImpactY2, 
msp.TotFinImpactY3 
      
FROM  isim.modelproduct mp

JOIN isim.ModelProductDetail mpd 
ON mp.modelproductid = mpd.modelproductid

JOIN isim.preference ProdPref 
ON mp.productid = ProdPref.prefskey 
 
JOIN isim.preference SubProdPref 
ON mpd.subproductid = SubProdPref.prefskey

JOIN ISIM.MCProduct MC 
ON mpd.modelproductdetailid = MC.modelproductdetailid

JOIN isim.modelscenario MS 
ON mp.modelid = MS.modelID  

JOIN isim.modelscenproduct MSP 
ON ms.modelscenarioid = msp.modelscenarioid
AND mpd.modelproductid = MSP.modelproductID  --product must be part of the scenario.
AND mpd.whatifnum = MSP.whatifnum  --get the correct whatifnum


WHERE mp.modelid = 1197
AND prodpref.prefcd = 'MC'
AND MS.scenarionum = 1
AND MS.RowDeletedDT IS NULL
AND MP.RowDeletedDT IS NULL
AND MPD.RowDeletedDT IS NULL
AND MC.RowDeletedDT IS NULL
AND MSP.RowDeletedDT IS NULL

GROUP BY       mc.modelproductdetailid, mp.modelproductid, mpd.subproductid, SubProdPref.valuetext,
msp.TotFinImpactY0, msp.TotFinImpactY1, msp.TotFinImpactY2, msp.TotFinImpactY3

It returns the following results...


13286	2720	21	Life	1320	2340	1230	1230	0	0	0	0	17600	26550	54140	-1000
13287	2720	25	AD&D	1230	3420	2450	3450	0	0	0	0	17600	26550	54140	-1000
13288	2720	26	Auto	3450	4560	3450	4560	0	0	0	0	17600	26550	54140	-1000
13314	2720	10	Other	0	0	0	0	1230	1450	1450	14560	17600	26550	54140	-1000
13315	2720	10	Other	0	0	0	0	1240	2340	4530	1340	17600	26550	54140	-1000
13316	2720	10	Other	0	0	0	0	3450	3450	3450	2350	17600	26550	54140	-1000
13317	2720	10	Other	0	0	0	0	2340	6540	7560	6540	17600	26550	54140	-1000
13318	2720	10	Other	0	0	0	0	3340	3450	3450	3450	17600	26550	54140	-1000

What I need to happen is for it to combine all "Other" products and give me the combined sum for all of them on one line. How can I go about doing this? Also, if there is a better way to write this query, please let me know.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-18 : 10:08:12

Since you are grouping on this column "mc.modelproductdetailid" you'll get a record for each one. If you remove it grom your select and group by clauses, you'll get the desired results

Jim

btisdabomb
Starting Member

2 Posts

Posted - 2007-09-18 : 10:12:47

Thanks jimf! I can't believe I missed that. I think it was just one of those things I have been staring at for too long and just needed fresh eyes to have a look at it.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-18 : 10:22:57

Sometimes a coffee break can be the most productive thing to do!

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources