Why is SUM() and COUNT() returning different values?-CodePudding

I have two near identical queries and I'm trying to understand why they're returning different results. I would like to produce a table with a user_id and a food_orders column showing how many items each user has ordered. These queries produce this table but calculate different results in the food_orders column in some rows.

My questions are why is this and which should I use?

A simplified version of my queries are below.

Query 1 (using COUNT):

WITH order_made AS (
    SELECT restaurant_id,
           count(
                   CASE
                       WHEN item LIKE '%_angus' then 1
                       WHEN item LIKE '%_organic' then 1
                       WHEN item LIKE '%_lean' then 1
                       ELSE 0
                       END)
               AS burgers
    FROM mcdonalds.specialist_orders
    GROUP BY mcdonalds.specialist_orders.user_id
    UNION ALL
    SELECT restaurant_id,
           COUNT(
                   CASE
                       WHEN item LIKE 'salad%' THEN 1
                       WHEN item LIKE 'tomato%' THEN 1
                       WHEN item LIKE 'potatoes%' THEN 1
                       ELSE 0
                       END)
               AS vegetables
    FROM public.bulk_orders
    GROUP BY public.bulk_orders.user_id),

Query 2 (using SUM):

WITH orders_made AS (
    SELECT user_id, SUM(food_orders) AS food_orders
    FROM (SELECT user_id,
                   CASE
                       WHEN item LIKE '%_angus' then 1
                       WHEN item LIKE '%_organic' then 1
                       WHEN item LIKE '%_lean' then 1
                       ELSE 0
                       END
               AS food_orders
    FROM mcdonalds.specialist_orders
    UNION ALL
    SELECT user_id,
                   CASE
                       WHEN item LIKE 'salad%' THEN 1
                       WHEN item LIKE 'tomato%' THEN 1
                       WHEN item LIKE 'potatoes%' THEN 1
                       ELSE 0
                       END
               AS food_orders
    FROM public.bulk_orders
    GROUP BY user_id)

CodePudding user response：

Because COUNT function count when the value isn't NULL (include 0) if you don't want to count, need to let CASE WHEN return NULL

so if you remove ELSE 0 from your CASE WHEN or use ELSE NULL to instead, that result would be as same as SUM

WITH order_made AS (
    SELECT restaurant_id,
           count(
                   CASE
                       WHEN item LIKE '%_angus' then 1
                       WHEN item LIKE '%_organic' then 1
                       WHEN item LIKE '%_lean' then 1
                       END)
               AS burgers
    FROM mcdonalds.specialist_orders
    GROUP BY mcdonalds.specialist_orders.user_id
    UNION ALL
    SELECT restaurant_id,
           COUNT(
                   CASE
                       WHEN item LIKE 'salad%' THEN 1
                       WHEN item LIKE 'tomato%' THEN 1
                       WHEN item LIKE 'potatoes%' THEN 1
                       END)
               AS vegetables
    FROM public.bulk_orders
    GROUP BY public.bulk_orders.user_id),