Get ROLLUP to include rows for missing values

Question

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

Answer 1

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.