T-SQL: User defined column cannot be converted to int for ordering

Question

Table:

id  delivered
1   carrot
1   potato
1   potato
2   potato
3   carrot
3   carrot
3   potato

Desired output:

id  carrots     potatoes
3   2           1
1   1           2
2   0           1

I want to see the customers who ordered both at top.

I'm able to do the group by but when I try to order results, I receive this error:

Conversion failed when converting the varchar value 'carrots' to data type int.

How would you do it?

Code on sqlfiddle and here:

SELECT 
id,
SUM(CASE WHEN delivered = 'carrot' THEN 1 ELSE 0 END) AS 'carrots',
SUM(CASE WHEN delivered = 'potato' THEN 1 ELSE 0 END) AS 'potatoes'
FROM schedule
GROUP BY id
--ORDER BY carrots DESC, potatoes DESC  [This sort of works but not all the time. Prefer code after]
ORDER BY (CASE WHEN 'carrots' > 0 THEN 1 ELSE 0 END) + (CASE WHEN 'potatoes' > 0 THEN 1 ELSE 0 END)

T-SQL/Microsoft SQL Server Management Studio 2016.

Answer 1

Identifiers are quoted with " or [] or do not qoute them at all:

SELECT 
id,
SUM(CASE WHEN delivered = 'carrot' THEN 1 ELSE 0 END) AS carrots,
SUM(CASE WHEN delivered = 'potato' THEN 1 ELSE 0 END) AS potatoes
FROM schedule
GROUP BY id
ORDER BY carrots, potatoes;

---

EDIT:

SELECT 
  id,
  SUM(CASE WHEN delivered = 'carrot' THEN 1 ELSE 0 END) AS carrots,
  SUM(CASE WHEN delivered = 'potato' THEN 1 ELSE 0 END) AS potatoes
FROM schedule
GROUP BY id
ORDER BY SUM(CASE WHEN delivered = 'carrot' THEN 1 ELSE 0 END) + 
         SUM(CASE WHEN delivered = 'potato' THEN 1 ELSE 0 END);

or cte:

WITH cte AS (
  SELECT 
  id,
  SUM(CASE WHEN delivered = 'carrot' THEN 1 ELSE 0 END) AS carrots,
  SUM(CASE WHEN delivered = 'potato' THEN 1 ELSE 0 END) AS potatoes
FROM schedule
GROUP BY id
)
SELECT *
FROM cte
ORDER BY (CASE WHEN carrots > 0 THEN 1 ELSE 0 END) + (CASE WHEN potatoes > 0 THEN 1 ELSE 0 END)

db<>fiddle demo

Answer 2

Use this logic:

ORDER BY (MAX(CASE WHEN carrots > 0 THEN 1 ELSE 0 END) + 
          MAX(CASE WHEN potatoes > 0 THEN 1 ELSE 0 END)
         ) DESC

Although SQL Server accepts column aliases in the ORDER BY, it is not so keen their use in expressions. Note how they fail here.