Group by multiple values on same column in SQL-CodePudding

I have a table which has 2 columns: user_id - int representing user food_ordered - string representing which item the user ordered

There can be multiple rows of the same value, like:

(user_id) 100 | 'pizza'
(user_id) 100 | 'pizza'
(user_id) 100 | 'burger'
(user_id) 100 | 'pizza'
(user_id) 100 | 'sushi'

I want to get a ratio of the number of times the user has ordered pizza over all orders. So, for above, I would want:

(user_id) 100 | 0.6 (user ordered pizza 3 times, 5 total orders)

I currently can GROUP BY on food_ordered, but I'd like to get 2 output columns (one for PIZZA, one for all types), then I can do pizza_orders / all_orders. How do I do a grouping like this?

CodePudding user response：

Try an aggregation:

SELECT user_id,
       1.0 * COUNT(CASE WHEN food_ordered = 'pizza' THEN 1 END) /
             COUNT(*) AS ratio_pizza_ordered
FROM yourTable
GROUP BY user_id;