August 2, 2007 at 11:59 am
Hi, when using a select case query how can I get a total row calculation of columns. Here is an example of the code I am using.
select a.item1,
sum (case
when a.item1 in ('value1', 'value2', 'value3')
then 1
else 0
end) as 'abc',
sum (case
when a.item1 in ('value1', 'value2', 'value3')
then 1
else 0
end) as 'def',
from tbl1 as a join tbl2 as b
on a.item1 = b.item1 where datepart(dd, getdate()) = datepart(dd, datefield1)
and b.item2 is not null and reasoncode IN ( 'g', ' h', 'i', 'j' )
group by a.item1
August 2, 2007 at 1:27 pm
you can use a sub query and then group on it
select a.item1,sum('abc'),sum('def')
from
(
select a.item1,
case
when a.item1 in ('value1', 'value2', 'value3')
then 1
else 0
end as 'abc',
case
when a.item1 in ('value1', 'value2', 'value3')
then 1
else 0
end as 'def',
from tbl1 as a join tbl2 as b
on a.item1 = b.item1 where datepart(dd, getdate()) = datepart(dd, datefield1)
and b.item2 is not null and reasoncode IN ( 'g', ' h', 'i', 'j' )
)
group by a.item1
August 3, 2007 at 8:59 am
Thanks for your response Bledu I've tried your suggestion which makes sense, but I'm getting a syntax error saying:
Incorrect syntax near the keyword 'group'.
Here is my query...I can't seem to modify it correctly, can anyone point out where the syntax error is? Thx
select
a.workgrpid, sum('BUS') AS bus_count, sum('CABLE') as cable_count,
sum('LC') as lc_count, sum('RES') as res_count
from
(
select
a.workgrpid,
case
when a.workgrpid in ('416ML CORE', 'work905W ML',
'KINGURBML'
, '416ML WEST', 'OTTURB ML', '416C MST', '905E ML',
'416ML NRTH'
, '416ML NRTH', '416ML EAST', '519W ML', '519E ML',
'416ML MTWN'
, '905C ML', '905 MST')
then 1
else 0
end as 'BUS',
case
when a.workgrpid in ('519W ANM', '519BACKLOG', '416 ANM W',
'905W DMD'
, '416BACKLOG', '905BACKLOG', '416 ANM E', '905 E DMD', '519E ANM',
'613BACKLOG'
)
then 1
else 0
end as 'CABLE',
case
when a.workgrpid in ('BOW GTA')
then 1
else 0
end as 'LC',
case
when a.workgrpid in ('GLPH URBAN', 'OSH/COBG', 'NIAGARA', '416 CORE',
'MAVIS/OAKV'
, 'KITCHENER', 'BELL 705', 'OTTAWA', 'OTT URBAN', 'HAM/BURL', 'KING', 'WIN/LEAM',
'WIND URBAN'
, 'BRAMPTON', 'NEWMARKET', '416 MIDTWN', 'LONDON', '416 NORTH', 'KIT URBAN',
'416 WEST'
, 'BROCK/SMFS', 'BTFD', 'LOND URBAN', 'MUSK', 'KING URBAN', 'CORN/HAWK',
'BLVL'
, '416DOWNTOWN', 'ESNA')
then 1
else 0
end as 'RES',
case
when a.workgrpid in ('BRC 519E', 'BRC 519W', 'BRC 705S', 'SSM', 'BRC OTTAWA',
'THUNDERBAY'
, 'BRC BLVKNG', 'NBAY')
then 1
else 0
end as 'BARC'
from
MCommPLOrders as a join workgroups as b
on
a.workgrpid = b.workgrpid COLLATE Latin1_General_CI_AS
where
datepart(dd, getdate()) = datepart(dd, TimeOfCode)
and
market is not null and reasoncode IN ('pl', 'pl2', 'r2', 't2')
)
group
by a.workgrpid, bus_count, cable_count, lc_count, res_count
August 3, 2007 at 3:08 pm
unless if its a typo you did not alias your sub query and you do not need to include the columns to be agregated in the group by, you just need to group by on the workgrpid
i.e.
select A.workgrpid, sum(A.[BUS]) AS bus_count, sum(A.[CABLE]) as cable_count,
sum(A.[LC]) as lc_count, sum(A.[RES]) as res_count
from
(
YOUR SUB QUERY ....
) A
group by A.workgrpid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply