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.
Identifiers are quoted with
"
or[]
or do not qoute them at all:EDIT:
or cte:
db<>fiddle demo