Postgres totals by classification per group

for example i have an orders table with a classification column. i want to get the total per classification for each city in a state for certain identified cities, for specific date.

I have done the following, which works.

SELECT
       state.id                                             AS state_id,
       state.name                                           AS state_name,
       city.id                                                    AS city_id,
       city.name                                                  AS city_name,
       count(*)                                                            AS total,
       count(CASE WHEN o.classification = 'A' THEN 1 END) AS total_A,
       count(CASE WHEN o.classification = 'B' THEN 1 END) AS total_B,
FROM orders AS o
         LEFT JOIN city ON o.city_id = city.id             
         LEFT JOIN state ON city.state_id = state.id
WHERE o.category = 'CATEGORY'
  AND o.city_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)
   AND (o.trn_date::date = '2020-05-07')
GROUP BY state.id, state.name, city.id, city.name
ORDER BY state.name, city.name;

The problem is that i don’t always get the same number (16) of cities specified in the in clause.

How can i always get 16 records even if they are all zero for some

Answer

You need to change your join order, move the orders conditions to the left join, and use filter expressions on your count() columns.

SELECT state.id    AS state_id,
       state.name  AS state_name,
       city.id     AS city_id,
       city.name   AS city_name,
       count(*) FILTER (WHERE o.city_id IS NOT NULL) AS total,
       count(*) FILTER (WHERE o.classification = 'A') AS total_A,
       count(*) FILTER (WHERE o.classification = 'B') AS total_B
  FROM state 
       JOIN city on city.state_id = state.id
       LEFT JOIN orders AS o
              ON o.city_id = city.id             
             AND o.category = 'CATEGORY'
             AND o.trn_date::date = '2020-05-07'
 WHERE city.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)
 GROUP BY state.id, state.name, city.id, city.name
 ORDER BY state.name, city.name;