MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » August 2014

Re: Another SQL question



fixed

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'
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'
,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'
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?

Thanks,
lance


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






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact