Subject: Re: Another SQL question From: Sam_L Date: Mon, 25 Aug 2014 17:45:23 -0500 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

I'd guess something like this:

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

Sam

On 8/25/2014 4:33 PM, Lance Gillespie wrote:
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'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APRTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAYTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUNTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JULTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUGTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEPTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCTTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOVTOT'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DECTOT'
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'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APRTOT', DscTotal AS 'APRDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAYTOT', DscTotal AS 'MAYDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUNTOT', DscTotal AS 'JUNDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JULTOT', DscTotal AS 'JULDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUGTOT', DscTotal AS 'AUGDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEPTOT', DscTotal AS 'SEPDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCTTOT', DscTotal AS 'OCTDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOVTOT', DscTotal AS 'NOVDSC'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DECTOT', DscTotal AS 'DECDSC'
GROUP BY YEAR(OrderDate)
ORDER BY [Year]

But of course that is not the correct syntax.

Am I just approaching the whole thing wrong?

Thanks,
lance

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com