What am I doing wrong with this query

Question

Good evening, I'm currently working on an SQL case study that involves solving this problem.

What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?

The plan is to create a query with a case statement for each topping. If a pizza has an extra topping it will count as 2 for the corresponding topping column.

Table with the pizza_id's and their corresponding toppings

pizza_id	toppings
1	1, 2, 3, 4, 5, 6, 8, 10
2	4, 6, 7, 9, 11, 12

Pizza_toppings

topping_id	topping_name
1	Bacon
2	BBQ Sauce
3	Beef
4	Cheese
5	Chicken
6	Mushrooms
7	Onions
8	Pepperoni
9	Peppers
10	Salami
11	Tomatoes
12	Tomato Sauce

When I apply the query below

 ;with ingredient_cte as (
 select c.order_id, c.pizza_id, exclusions, extras,
 case
     when c.pizza_id = 1 then 1
     when extras in ('1', '1', '1, 4') then 2
     else 0 
 end bacon,
 case
     when c.pizza_id = 1 or c.order_id != 10 and exclusions != '2, 6' then 1
     else 0
 end bbq_sauce,
 case
     when c.pizza_id = 1 then 1
     else 0 
 end beef,
 case
     when c.pizza_id in (1, 2) or exclusions != '4' then 1
     else 0
 end cheese
 from customer_orders c
 join runner_orders r
     on c.order_id = r.order_id
join pizza_names n
     on c.pizza_id = n.pizza_id
where cancellation is null
)
select *
from ingredient_cte

I get this table

order_id	pizza_id	exclusions	extras	bacon	bbq_sauce	beef	cheese
1	1	NULL	NULL	1	1	1	1
2	1	NULL	NULL	1	1	1	1
3	1	NULL	NULL	1	1	1	1
3	2	NULL	NULL	0	0	0	1
4	1	4	NULL	1	1	1	1
4	1	4	NULL	1	1	1	1
4	2	4	NULL	0	1	0	1
5	1	NULL	1	1	1	1	1
7	2	NULL	1	2	0	0	1
8	1	NULL	NULL	1	1	1	1
10	1	NULL	NULL	1	1	1	1
10	1	2, 6	1, 4	1	1	1	1

The problem with my query is that it's returning some incorrect responses from some of my case statements, e.g. The bacon column in the last row be 2 since it includes extra bacon (1, 4). Also, the bacon row for order_id 5 should also be 2 which is why I used when extras in ('1', '1', '1, 4') then 2 as part of my query.

Answer 1

To fix my query, I had to rearrange the structure of the case statement by starting with the conditions involving extra toppings (extras) and or excluded toppings (exclusions) before creating the condition for the toppings and their corresponding pizza, e.g. Bacon goes with Meatlovers aka pizza_id 1. I hope my updated explanation makes sense and sorry for wasting your time.

;with ingredient_cte as ( select c.order_id, customer_id, 
c.pizza_id, exclusions, extras,
case
    when extras = '1' then 2
    when extras = '1, 4' then 2
    when c.pizza_id = 1 then 1
    else 0 
end bacon,
case
    when exclusions = '2, 6' then 0
    when c.pizza_id = 1 then 1
    else 0 
end bbq_sauce,
case
    when c.pizza_id = 1 then 1
    else 0 
end beef,
case
    when exclusions = '4' then 0
    when extras = '1, 4' then 2
    when c.pizza_id in (1, 2) then 1
end cheese,
case
    when c.pizza_id = 1 then 1
    else 0 
end chicken,
case
    when exclusions = '2, 6' then 0
    when c.pizza_id = 2 then 1
    else 0
end mushrooms,
case
    when c.pizza_id = 2 then 1
    else 0 
end onions,
case
    when c.pizza_id = 1 then 1
    else 0
end pepperoni,
case
    when c.pizza_id = 2 then 1
    else 0
end peppers,
case
    when c.pizza_id = 1 then 1
    else 0 
end salami,
case
    when c.pizza_id = 2 then 1
    else 0
end tomatoes,
    case
    when  c.pizza_id = 2 then 1
    else 0
end tomato_sauce
from customer_orders c
join runner_orders r
    on c.order_id = r.order_id
join pizza_names n
    on c.pizza_id = n.pizza_id
where cancellation is null
)
select sum(bacon) bacon, sum(cheese) cheese, sum(beef) beef, 
sum(chicken) chicken, sum(pepperoni) pepperoni, 
sum(salami) salami, sum(bbq_sauce) bbq_sauce, sum(mushrooms) 
mushrooms, sum(onions) onions, sum(peppers) peppers, 
sum(tomatoes) tomatoes, sum(tomato_sauce) tomato_sauce
from ingredient_cte