ORDER BY items must appear in the select list

[mjd3000]

Can somebody please tell me what is wrong with this SQL? I am getting the following error whenever I add the final line :

'ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator'


select * from
(
SELECT top 5 COUNT(*) AS 'TotalEnquiries',
f.createdbyname as 'Name',

round(100 * CAST(COUNT(*) as float) /
(SELECT COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'), 0) AS 'Percentage'
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid

where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'

GROUP BY f.createdbyname

order by totalenquiries desc) as d

union

select
(
SELECT SUM(T2.Expr1) FROM
(
(SELECT COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'
GROUP BY f.createdbyname)
) t2) -

(SELECT SUM(T1.Expr1) FROM
(
(SELECT top 5 COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'
GROUP BY f.createdbyname order by count(*) desc)) t1)

, 'Other',

round(100*(cast(((SELECT SUM(T2.Expr1) FROM
(
(SELECT COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'
GROUP BY f.createdbyname)
) t2) -

(SELECT SUM(T1.Expr1) FROM
(
(SELECT top 5 COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'
GROUP BY f.createdbyname order by count(*) desc)) t1)) as float))/

(SELECT COUNT(*) AS Expr1
from systemuser s
inner join filteredlead f
on s.systemuserid = f.ownerid
where createdonutc
between DateAdd(Day, DateDiff(Day, 0, GetDate()-6), 0)
And DateAdd(day, DateDiff(Day, 0, GetDate() + 1), 0)
and s.businessunitidname <> 'External Agencies'), 0)

ORDER BY CASE WHEN Name = 'Other' Then 1 ELSE 0 END, percentage

 

[drlex]

It's actually a pretty informative error message.
Your ORDER by statement is invalid as the field by which you wish to order is one of those in the SELECT statement; the SELECT statement having a UNION clause.
As per a suggestion in one of your other threads, why not use a temp table to store your results and then ORDER a SELECT from that?

soi la, soi carré

 

[mjd3000]

I don't get it though...I am able to order by Percentage or by Name, but when I add the CASE part, I get this error

 

[drlex]

That is because you have named two calculated fields 'Name' and 'Percentage' and they are being returned in the "SELECT * from ([Complicated SELECT statement])". You do not have a calculated field CASE WHEN Name = 'Other' Then 1 ELSE 0 END in the SELECT, hence the error.


soi la, soi carré