Group and include all categories in SQL

Question

I need to select all groups in a table for each category, even if that group is missing for a given category (and put 0 or NULL as value) I need to do this via a SQL query (Impala).

An example is reported below (basically I need to dynamically display also the last row in the second table).

Category     Group     Amount              Category     Group     Amount
+--------------------------------+          +--------------------------------+
   A           X          1                    A           X          1
   A           Y          2                    A           Y          2
   A           Z          5           ->       A           Z          5
   B           X          2                    B           X          2
   B           Y          3                    B           Y          3
                                               B           Z          0

Anyone knows how to achieve this? Thanks!

Answer 1

You need a Cross Join of the categories and the groups first and then a Left Join:

select c.category, g.group, coalesce(amount, 0)
from
 ( -- all categories
   select distinct Category from tab
 ) as c
cross join -- create all possible combinations
 ( -- all groups
   select distinct group from tab
 ) as g
left join tab as a -- now join back the amount
  on c.category = a.category
 and g.group = a.Group