August 16, 2011 at 4:59 am
Hi,
I am working on a query where I need to do count when a certain condition is met:
this is what I have tried but it doesnt work and gives an error (Column 'ONTIME_S' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.).
I cannot group by Ontime_s, Ontime_c, Late_s or Late_c as that is not an option and will make the report work incorrectly.
-----------------------------------------------------------------------------------
SELECT
ESO_PORT,
case when ONTIME_S = 'On Time' then count(BL_NUM) end as Ontime_s,
case when ONTIME_S = 'Late' then count(BL_NUM) end as Late_s,
case when ONTIME_C = 'On Time' then count(BL_NUM) end as Ontime_c,
case when ONTIME_C = 'Late' then count(BL_NUM) end as Late_c
FROM MG_EU24TIMELINESS_DATASET
Group by ESO_PORT
Could someone please help me in this regard ?
Thanks,
Paul
August 16, 2011 at 5:29 am
I believe this will work for you.
SELECT ESO_PORT,
SUM(Ontime_s) Ontime_s,
SUM(Late_s) Late_s,
SUM(Ontime_c) Ontime_c,
SUM(Late_c) Late_c
FROM (SELECT
ESO_PORT,
case when ONTIME_S = 'On Time' then 1 else 0 end as Ontime_s,
case when ONTIME_S = 'Late' then 1 else 0 end as Late_s,
case when ONTIME_C = 'On Time' then 1 else 0 end as Ontime_c,
case when ONTIME_C = 'Late' then 1 else 0 end as Late_c
FROM MG_EU24TIMELINESS_DATASET)
GROUP BY ESO_PORT
August 16, 2011 at 5:36 am
Charles Hearn (8/16/2011)
I believe this will work for you.
SELECT ESO_PORT,SUM(Ontime_s) Ontime_s,
SUM(Late_s) Late_s,
SUM(Ontime_c) Ontime_c,
SUM(Late_c) Late_c
FROM (SELECT
ESO_PORT,
case when ONTIME_S = 'On Time' then 1 else 0 end as Ontime_s,
case when ONTIME_S = 'Late' then 1 else 0 end as Late_s,
case when ONTIME_C = 'On Time' then 1 else 0 end as Ontime_c,
case when ONTIME_C = 'Late' then 1 else 0 end as Late_c
FROM MG_EU24TIMELINESS_DATASET)
GROUP BY ESO_PORT
Thanks Charles, I tried your query but gives an error:
Incorrect syntax near the keyword 'GROUP'
I cant find any syntax error as such, would you know what needs to be changed ?
August 16, 2011 at 5:40 am
Sorry about that. You need to add an alias to the end of the subquery.
SELECT ESO_PORT,
SUM(Ontime_s) Ontime_s,
SUM(Late_s) Late_s,
SUM(Ontime_c) Ontime_c,
SUM(Late_c) Late_c
FROM (SELECT
ESO_PORT,
case when ONTIME_S = 'On Time' then 1 else 0 end as Ontime_s,
case when ONTIME_S = 'Late' then 1 else 0 end as Late_s,
case when ONTIME_C = 'On Time' then 1 else 0 end as Ontime_c,
case when ONTIME_C = 'Late' then 1 else 0 end as Late_c
FROM #MG_EU24TIMELINESS_DATASET) M
GROUP BY ESO_PORT
August 16, 2011 at 5:41 am
Stick an alias on the subquery (just add AS Sub after the close of the bracket for the subquery)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2011 at 5:48 am
Charles Hearn (8/16/2011)
Sorry about that. You need to add an alias to the end of the subquery.
SELECT ESO_PORT,SUM(Ontime_s) Ontime_s,
SUM(Late_s) Late_s,
SUM(Ontime_c) Ontime_c,
SUM(Late_c) Late_c
FROM (SELECT
ESO_PORT,
case when ONTIME_S = 'On Time' then 1 else 0 end as Ontime_s,
case when ONTIME_S = 'Late' then 1 else 0 end as Late_s,
case when ONTIME_C = 'On Time' then 1 else 0 end as Ontime_c,
case when ONTIME_C = 'Late' then 1 else 0 end as Late_c
FROM #MG_EU24TIMELINESS_DATASET) M
GROUP BY ESO_PORT
Thanks a ton, Charles , it worked like a charm !!! I really appreciate your help.
August 16, 2011 at 5:49 am
GilaMonster (8/16/2011)
Stick an alias on the subquery (just add AS Sub after the close of the bracket for the subquery)
Thanks very much for your help !
August 16, 2011 at 8:15 am
Or just remove the subquery as it really isn't needed.
SELECT
ESO_PORT,
sum(case when ONTIME_S = 'On Time' then 1 else 0 end) as Ontime_s,
sum(case when ONTIME_S = 'Late' then 1 else 0 end) as Late_s,
sum(case when ONTIME_C = 'On Time' then 1 else 0 end) as Ontime_c,
sum(case when ONTIME_C = 'Late' then 1 else 0 end) as Late_c
FROM MG_EU24TIMELINESS_DATASET
GROUP BY ESO_PORT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 9:36 am
Sean Lange (8/16/2011)
Or just remove the subquery as it really isn't needed.
SELECT
ESO_PORT,
sum(case when ONTIME_S = 'On Time' then 1 else 0 end) as Ontime_s,
sum(case when ONTIME_S = 'Late' then 1 else 0 end) as Late_s,
sum(case when ONTIME_C = 'On Time' then 1 else 0 end) as Ontime_c,
sum(case when ONTIME_C = 'Late' then 1 else 0 end) as Late_c
FROM MG_EU24TIMELINESS_DATASET
GROUP BY ESO_PORT
Thanks a lot, Sean, that sure is another way of doing it ! but seriously thanks to all the contributors for their time and effort, you are just amazing !
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply