How can i order by a case statement thats in a sub query-CodePudding

Hi all first time poster learning (MS)SQL :) - I hope you can help. I have the below query but would like to order it with highest paying category coming first.

If i try an order by salaries within the subquery, i'm told thats not allowed.

Msg 1033, Level 15, State 1, Line 60
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.If i try it outside of the query it tells me its also not allowed as salaries is not in the group by

Select test.rating, COUNT(rating) as total
FROM (
  select ID, name, salaries, 
         CASE
             WHEN salaries \> 12345 THEN 'paid well'
             WHEN salaries \< 12345 THEN 'underpaid'
             WHEN salaries = 12345  THEN 'average'
             ELSE 'null'
         END AS rating
  from dupes
) test
GROUP by test.rating

From above this is my current output and exactly how I want it, but would like the Paid well category first, followed by average, then underpaid. Can anyone please help me?

rating     total

average      2


null                 5

underpaid     4  

paid well   
    1

CodePudding user response：

Try this:

SELECT test.rating, COUNT(rating) AS total
FROM (
  select ID, name, salaries, 
         CASE
             WHEN salaries \> 12345 THEN 'paid well'
             WHEN salaries \< 12345 THEN 'underpaid'
             WHEN salaries = 12345  THEN 'average'
             ELSE 'null'
         END AS rating
  from dupes
) test
GROUP by test.rating
ORDER BY
   CASE
       WHEN test.rating = 'Paid Well' THEN 1
       WHEN test.rating = 'Average' THEN 2
       WHEN test.rating = 'Underpaid' THEN 3
       WHEN test.rating = 'null' THEN 99
       ELSE 4
   END

CodePudding user response：

Just add a ORDER BY after the GROUP BY (ie it will be the last action performed):

ORDER BY
   CASE WHEN test.rating='null' then 99
        WHEN test.rating='Paid Well' then 1
        WHEN test.rating='Average' then 2
        WHEN test.rating='Underpaid' then 3
        ELSE 4
   END

CodePudding user response：

WITH detaildata AS (SELECT test.rating, COUNT(rating) AS total FROM ( select ID, name, salaries, CASE WHEN salaries \> 12345 THEN 'paid well' WHEN salaries \< 12345 THEN 'underpaid' WHEN salaries = 12345  THEN 'average' ELSE 'null' END AS rating from dupes ) test GROUP by test.rating ) SELECT rating, total FROM detaildata ORDER BY rating