How to add a category to the results of a query based on the output?

Extending this question for a more general case:

I have a table called fruits with a column fruit that has three possible values: apple, orange, and mango. I used the following query to get the counts of each fruit by date:

SELECT date, fruit, COUNT(id) AS count
FROM fruits
WHERE date BETWEEN '2020-10-02' AND '2020-10-05'
GROUP BY date, fruit

that gives the following results:

date           fruit      count
--------------------------------
2020-10-02     apple        3
2020-10-02     orange       5
2020-10-03     orange       23
2020-10-03     mango        1
2020-10-04     mango        9
2020-10-05     apple        10
2020-10-05     orange       7
2020-10-05     mango        6

As you can see, not every date contains each type of fruit. Is it possible, and if so, how, to add the missing fruit by each date to the above results with a default count=0, as shown below?

date           fruit      count
--------------------------------
2020-10-02     apple        3
2020-10-02     orange       5
2020-10-02     mango        0
2020-10-03     orange       23
2020-10-03     mango        1
2020-10-03     apple        0
2020-10-04     mango        9
2020-10-04     apple        0
2020-10-04     orange       0
2020-10-05     apple        10
2020-10-05     orange       7
2020-10-05     mango        6

Answer

Use a cross join ot generate the row and then a left join to bring in the results:

select d.date, f.fruit, coalesce(ff.count, 0) as count
from (select distinct date from fruits) d cross join
     (select distinct fruit from fruits) f left join
     fruits ff
     on ff.date = d.date and ff.fruit = f.fruit;

Advertisement

Answer