Home > Software design >  Why is SUM() and COUNT() returning different values?
Why is SUM() and COUNT() returning different values?

Time:02-12

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),
  • Related