PostgreSQL 14
Assuming this sample data:
| fruit | ripeness |
|---|---|
| orange | 1 |
| orange | 3 |
| apple | 0 |
| apple | 3 |
| apple | 3 |
| apple | 2 |
Is there a way to get ROLLUP to give me a count of fruits by ripeness with a row for each possible value of ripeness like this:
| fruit | ripeness | count |
|---|---|---|
| orange | 0 | 0 |
| orange | 1 | 1 |
| orange | 2 | 0 |
| orange | 3 | 1 |
| orange | 2 | |
| apple | 0 | 1 |
| apple | 1 | 0 |
| apple | 2 | 1 |
| apple | 3 | 2 |
| apple | 4 | |
| 6 |
Instead of only the existing values in the group appearing like this:
| fruit | ripeness | count |
|---|---|---|
| orange | 1 | 1 |
| orange | 3 | 1 |
| orange | 2 | |
| apple | 0 | 1 |
| apple | 2 | 1 |
| apple | 3 | 2 |
| apple | 4 | |
| 6 |
>Solution :
Cross join the distinct values of fruit to the distinct values of ripeness and do a LEFT join to the table.
Then aggregate with ROLLUP:
SELECT f.fruit, r.ripeness,
COUNT(t.fruit) counter
FROM (SELECT DISTINCT fruit FROM tablename) f
CROSS JOIN (SELECT DISTINCT ripeness FROM tablename) r
LEFT JOIN tablename t ON t.fruit = f.fruit AND t.ripeness = r.ripeness
GROUP BY ROLLUP(f.fruit, r.ripeness)
ORDER BY f.fruit, r.ripeness;
See the demo.