sum of second column not working

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

General SQL Server Forums

New to SQL Server Programming

sum of second column not working

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-08-25 : 18:25:38

I need to sum two different values into two different totals by month, probably using a case statement.

I can make the classic SUM CASE WHEN work just fine:

SELECT [Year]=YEAR(OrderDate),
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JANTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEBTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MARTOT'
etc.

FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY [Year]

But each record also has a discount that I want totaled something like:

SELECT [Year]=YEAR(OrderDate),
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JANTOT', DscTotal AS 'JANDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEBTOT', DscTotal AS 'FEBDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MARTOT', DscTotal AS 'MARDSC'
etc.

FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY [Year]

But of course that is not the correct syntax.

Am I just approaching the whole thing wrong?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-25 : 18:55:04

Depends on whether you want one field with everything summed, per month:

SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal-DscTotal ELSE 0 END) AS 'JANTOT'

or you want two fields:

SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal ELSE 0 END) AS 'JANTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 1 THEN DscTotal ELSE 0 END) AS 'JANDSC'

It's always good pratice to include "else statement" whenever you're using case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources