Re: Another SQL question

On 25-Aug-2014 16:33 -0500, 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'
<<SNIP>>
,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]

Seems from the example, that there is no [obvious] point to have obtained the data as\across columns versus as rows. It would seem to me to be much simpler, written as a /classic aggregate query/ without any CASE expressions; just returning the data as rows, instead of as one row (per year) of many columns:

select YEAR(OrderDate) as YR
, MONTH(OrderDate) as MO
, SUM(SubTotal) as MOSUBTOT
from Sales.SalesOrderHeader as SOH
group by YEAR(OrderDate)
, MONTH(OrderDate)
order by 1, 2

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'
<<SNIP>>
,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.

Back to the /classic aggregate query/ without any CASE expressions, adding a new aggregate for the same grouping is quite intuitive:

select YEAR(OrderDate) as YR
, MONTH(OrderDate) as MO
, SUM(SubTotal) as MOSUBTOT
, SUM(DscTotal) as MODSCTOT /* additional summary */
from Sales.SalesOrderHeader as SOH
group by YEAR(OrderDate) /* note: grouping unchanged */
, MONTH(OrderDate)
order by 1, 2

Am I just approaching the whole thing wrong?

If the data is required to be obtained in columns instead of rows, then designing a /pivot query/ over that data might be simpler; both for the original query\data and for the updated query having since included the additional summation. Both the original and the pivot queries can be encapsulated in a VIEW [each their own separate VIEW; the pivot in a VIEW over the summary VIEW; without ordering], though ordering is restricted to the final query.