Answered by:
aggregate functions
Question

I'm trying to get the query below to add an additional column that will give me the %s compared to the total of the "RoadCalls 07" column. The query below works fine, but when I try to add the additional column I run into problems. Any help would be appreciated..
select wsshop as Shop,
sum (case
when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end) as 'RoadCalls 07',
sum (case
when wsrty not like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end) as 'NonRoadCalls 07'
FROM faster.wsublet
WHERE wsdateposted between 20040701 and 20080630
GROUP BY wsshop
order by wsshop
I tried the below, but I can't get it to work. Do I need to include aliases, and if so how should it be done... I'm totally frustrated...select
wsshop as Shop,
sum (case
when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end) as 'RoadCalls 07',
sum (case
when wsrty not like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end) as 'NonRoadCalls 07',
sum (case
when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end)/(SELECT SUM(sum (case
when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
then wstotalcost
else 0 end)))AS '% of Totals'
FROM faster.wsublet
WHERE wsdateposted between 20040701 and 20080630
GROUP BY wsshop Edited by reneelacks Wednesday, June 17, 2009 2:37 AM
Wednesday, June 17, 2009 2:35 AM
Answers

Try these queries:
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
MAX(total_non_road) AS [% of Totals]
FROM (
SELECT wsshop, wsrty, wstotalcost,
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) OVER() AS total_non_road
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
GROUP BY wsshop;
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
(
SELECT SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END)
FROM faster.wsublet
) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop;
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop;
 Plamen Ratchev Marked as answer by reneelacks Wednesday, June 17, 2009 6:29 PM
Wednesday, June 17, 2009 2:14 PM
All replies

Try this:
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
AND wsdateposted BETWEEN 20060701 AND 20070630
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
AND wsdateposted BETWEEN 20060701 AND 20070630
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
AND wsdateposted BETWEEN 20060701 AND 20070630
THEN wstotalcost
ELSE 0 END) * 1.0/
SUM(CASE
WHEN wsdateposted BETWEEN 20060701 AND 20070630
THEN wstotalcost
ELSE 0 END) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20040701 AND 20080630
GROUP BY wsshop;
BTW, it almost seems you just need to change the predicate for the date
and then all expressions will be simplified:
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
SUM(wstotalcost) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop;

Plamen Ratchev
http://www.SQLStudio.comWednesday, June 17, 2009 3:55 AM 
this is close, but the column I need "summed" is the resulting column 'NonRoadCalls 07', which of course it won't let me do. See the adjustment to your query... or, in other words, if I added "with rollup" to my GROUP BY Clause, that's the figure I need to do my calculations on...
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
SUM(NonRoadCalls 07) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop with rollup;
Non % of % of
SHOP RoadCalls07 RoadCalls07 Totals Totals
NULL 433819.72 5913842.51 0.931656 1.00000
ADM 845.00 29094.27 0.971776 should be 0.004920
BOD 5721.93 1460446.15 0.996097 0.246954
BRV 180.00 35837.84 0.995002 0.006060 etc.
CAR 1732.00 197718.04 0.991316
CVT 1050.00 359478.19 0.997087
FIR 6984.89 570667.61 0.987908
FWY 4640.88 322781.21 0.985826
HEQ 25574.03 285495.91 0.917786
LIB 0.00 39738.21 1.000000
LWN 1720.00 292318.88 0.994150
MEC 125.00 31374.17 0.996031
PRD 525.00 17696.50 0.971187
PUL 881.00 476727.03 0.998155
RPR 1433.22 484383.49 0.997049
TIR 239683.91 103346.68 0.301275
TRK 142612.86 1041286.74 0.879539
YRK 110.00 165451.59 0.999335Wednesday, June 17, 2009 7:20 AM 
Hi Rene, Plaman,
Have you heard about the Group By Grouping Sets a TSQL improvement with MS SQL Server 2008
Group By Grouping Sets gives more control over grouping and aggregation during select statements.
I think it worths a quick look.
Eralper
TSQL and SQL Server 2008
http://www.kodyaz.com http://www.eralper.comWednesday, June 17, 2009 7:31 AM 
You could get that by using a CTE:
WITH cte AS ( SELECT wsshop AS Shop ,SUM(CASE WHEN wsrty LIKE '9%' THEN wstotalcost ELSE 0 END) AS [RoadCalls 07] ,SUM(CASE WHEN wsrty NOT LIKE '9%' THEN wstotalcost ELSE 0 END) AS [NonRoadCalls 07], FROM faster.wsublet WHERE wsdateposted BETWEEN 20060701 AND 20070630 GROUP BY wsshop with rollup ) SELECT wsshop ,[RoadCalls 07] ,[NonRoadCalls 07] ,[NonRoadCalls 07]/SUM(CASE WHEN wsshop IS NULL THEN 0 ELSE [NonRoadCalls 07] END) OVER () AS [% of Totals] FROM cte;
BradWednesday, June 17, 2009 1:05 PM 
Yes, but grouping sets do not help to solve this problem.

Plamen Ratchev
http://www.SQLStudio.comWednesday, June 17, 2009 2:04 PM 
Try these queries:
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
MAX(total_non_road) AS [% of Totals]
FROM (
SELECT wsshop, wsrty, wstotalcost,
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) OVER() AS total_non_road
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
GROUP BY wsshop;
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
(
SELECT SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END)
FROM faster.wsublet
) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop;
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [% of Totals]
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
GROUP BY wsshop;
 Plamen Ratchev Marked as answer by reneelacks Wednesday, June 17, 2009 6:29 PM
Wednesday, June 17, 2009 2:14 PM 
Plamen... can't thank you enough. This one below worked like a charm. If not too much trouble, can you explain what the query is doing, expecially confused by the "OVER"..... and why Aias "T"
and thanks for everyone's input
SELECT
wsshop AS Shop,
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [RoadCalls 07],
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) AS [NonRoadCalls 07],
SUM(CASE
WHEN wsrty LIKE '9%'
THEN wstotalcost
ELSE 0 END) * 1.0/
MAX(total_non_road) AS [% of Totals]
FROM (
SELECT wsshop, wsrty, wstotalcost,
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) OVER() AS total_non_road
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
GROUP BY wsshop; Edited by reneelacks Wednesday, June 17, 2009 6:38 PM
Wednesday, June 17, 2009 6:15 PM 
Only on 2005Wednesday, June 17, 2009 6:33 PM

Hate to sound so stupid, but I'm pretty new to SQL... CTE??? Totally lost, how does it work?Wednesday, June 17, 2009 6:35 PM

CTE means "Common Table Expression" (only available in SQL2005 and above).
It's like a temporary result set (like a derived table), and one can make an argument that its syntax is more clear
In my example, it's equivalent to writing the following:
SELECT wsshop ,[RoadCalls 07] ,[NonRoadCalls 07] ,[NonRoadCalls 07]/SUM(CASE WHEN wsshop IS NULL THEN 0 ELSE [NonRoadCalls 07] END) OVER () AS [% of Totals] FROM (SELECT wsshop AS Shop ,SUM(CASE WHEN wsrty LIKE '9%' THEN wstotalcost ELSE 0 END) AS [RoadCalls 07] ,SUM(CASE WHEN wsrty NOT LIKE '9%' THEN wstotalcost ELSE 0 END) AS [NonRoadCalls 07], FROM faster.wsublet WHERE wsdateposted BETWEEN 20060701 AND 20070630 GROUP BY wsshop with rollup) AS cte
It especially comes in handy when you don't want to repeat the same subquery multiple times. In the code I posted for you, the CTE was only referenced once. But it can be referenced multiple times in the main query, eliminating a lot of duplicate code.
BradWednesday, June 17, 2009 7:11 PM 
First, the subquery in the derived table simply calculates the total non
road cost for each row. The trick here is using an aggregate SUM
function with the OVER clause. That allows you to calculate any
aggregate based on partition. Normally you would use it like
SUM(columnA) OVER(PARTITION BY columnB). But in this case you need the
total amount, so you can skip the PARTITION BY clause and simply use
OVER(). You can read more about OVER here
http://msdn.microsoft.com/enus/library/ms189461.aspx?ppud=4.
SELECT wsshop, wsrty, wstotalcost,
SUM(CASE
WHEN wsrty NOT LIKE '9%'
THEN wstotalcost
ELSE 0 END) OVER() AS total_non_road
FROM faster.wsublet
WHERE wsdateposted BETWEEN 20060701 AND 20070630
All derived tables have to be aliased, so this is why the alias T.
The next step is simply to to calculate the summary columns (which you
already had correct) and to divide by the total non road calculated in
the derived table. Also, here you have to use MAX (or MIN, it doesn't
matter since the value is the same for all rows) to get the value (or
have to include it in the GROUP BY clause).

Plamen Ratchev
http://www.SQLStudio.comWednesday, June 17, 2009 8:28 PM