Order By Case Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Order By Case Statement Help

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2015-02-03 : 20:33:07

I have this query where I am trying to order by a case statement however I get invalid column for "Grade"??


SELECT        '12th' AS Grade, SUM(CASE WHEN AG_Schedule = 'Yes' THEN 1 ELSE 0 END) * 100 / COUNT(ID) AS Percentage, 'A-G Schedule' AS Indicator
FROM            ewiseSchema.Grade12CH3_table(@GR12, @SC) AS Grade12CH1_table_1
UNION
SELECT        '11th' AS Grade, SUM(CASE WHEN AG_Schedule = 'Yes' THEN 1 ELSE 0 END) * 100 / COUNT(ID) AS Percentage, 'A-G Schedule' AS Indicator
FROM            ewiseSchema.Grade11CH3_table(@GR11, @SC) AS Grade11CH1_table_1
UNION
SELECT        '10th' AS Grade, SUM(CASE WHEN AG_Schedule = 'Yes' THEN 1 ELSE 0 END) * 100 / COUNT(ID) AS Percentage, 'A-G Schedule' AS Indicator
FROM            ewiseSchema.Grade10CH3_table(@GR10, @SC, @dt1, @dt2, @year) AS Grade10CH1_table_1
UNION
SELECT        '9th' AS Grade, SUM(CASE WHEN AG_Schedule = 'Yes' THEN 1 ELSE 0 END) * 100 / COUNT(ID) AS Percentage, 'A-G Schedule' AS Indicator
FROM            ewiseSchema.Grade9CH3_table(@GR9, @SC, @dt1, @dt2, @year) AS Grade9CH1_table_1

ORDER BY Indicator, (CASE Grade WHEN '9th' THEN 1 WHEN '10th' THEN 2 WHEN '11th' THEN 3 WHEN '12th' THEN 4 END)

Cartesian Yak

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 20:56:02

Unfortunately, you cannot refer to aliased column names in a CASE expression in an ORDER BY clause. The solution is to wrap the query (all your unioned queries) in parenthesis, to turn it into a subquery:


select * from 
(
...your queries

)
ORDER BY ...

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2015-02-03 : 21:10:25

Thanks dude!!

Cartesian Yak

sacredsun
Starting Member

1 Post

Posted - 2015-02-03 : 21:34:57

i developed a record system for the sacred sun battery sales department .It used in telecom battery ,UPs battery and motive battery system .inside the system,i used bll-model-dal structure,it is very useful .
so i recommend it to you .

-------
my sacred sun ,my define

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-04 : 11:06:04

Or just add a space before the 9 in 9th grade, so that they naturally sort correctly:

SELECT ' 9th' AS Grade
...
ORDER BY Indicator, Grade

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources