Adding rows to SQL query for each element in WHERE sequence

Question

I have an SQL query like the following:

SELECT store_id, SUM(quantity_sold) AS count
FROM sales_table
WHERE store_id IN ('Store1', 'Store2', 'Store3')
GROUP BY store_id;

This returns a row for each store that has rows in sales_table, but does not return a row for those that do not. What I want is one row per store, with a 0 for count if it has no records.

How can I do this, assuming that I do not have access to a stores table?

Answer 1

with stores (store_id) as (
   values ('Store1'), ('Store2'), ('Store3')
)
select st.store_id, 
       sum(sal.quantity_sold) as cnt
from stores st
  left join sales_table sal on sal.store_id = st.store_id
group by st.store_id;

If you do have a stores table, then simply do an outer join to that one instead of "making one up" using the common table expression (with ..).

This can also be written without the CTE (common table expression):

select st.store_id, 
       sum(sal.quantity_sold) as cnt
from (
  values ('Store1'), ('Store2'), ('Store3')
) st
  left join sales_table sal on sal.store_id = st.store_id
group by st.store_id;

(But I find the CTE version easier to understand)

Answer 2

You can use unnest() to generate rows from array elements.

SELECT store, sum(sales_table.quantity_sold) AS count
FROM unnest(ARRAY['Store1', 'Store2', 'Store3']) AS store
LEFT JOIN sales_table ON (sales_table.store_id = store)
GROUP BY store;