RE: Another SQL question -- MIDRANGE-L

Good point and that would work for that data set.

I was trying for a general syntax for the "add two columns
in a sum case where" so that it would work for this issue as well:

create table qtemp/ut420piv as (
select utcsid, utlcid, bilprd,
sum(case when UTRBCM = 'MU' then UTTAMT end) as MUREV,
sum(case when UTRBCM = 'SC' then UTTAMT end) as SCREV,
sum(case when UTRBCM = 'T1' then UTTAMT end) as T1REV,
sum(case when UTRBCM = 'T2' then UTTAMT end) as T2REV,
sum(case when UTRBCM = 'T3' then UTTAMT end) as T3REV,
sum(case when UTRBCM = 'T4' then UTTAMT end) as T4REV,
sum(case when UTRBCM = 'T5' then UTTAMT end) as T5REV
from qtemp/UT420SUBST
group by utcsid, utlcid, bilprd
order by utcsid, utlcid, bilprd) with data

where I also want to sum from those same selections the
field UTCHCN into MUCONS, SCCONS, T1CONS, etc.

and the selection is not exhaustive. There are many records that
this selection omits.

I suppose I can run this twice, once to sum UTRBCM and once for UTCHCN and join the results.

Thanks,
lance

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, August 25, 2014 3:46 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.