Consider that with the view that was previously suggested, the system is far
more efficient at determining the result and will not force table scans at
query time. I believe all of the SQL presented would force a table scan, so
be sure to run whatever you come up with through the SQL optimizer with
debug turned on so it can suggest some help.
If the primary data set is less than 1000 rows then it won't matter, but if
these queries get up to several thousand or hundreds of thousands of rows
and you might be looking at a long running query.
--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Monday, August 25, 2014 5: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.
midrange.com
