× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.