Get ROLLUP to include rows for missing values

user_4842667 0
Viewed 95

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
rollup left-join cross-join group-by postgresql
2 Answers

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.

user_104988280

Solution without ROLLUP, naive but intuitive:

select * from (
  (
    select distinct f1.fruit, f2.ripeness, (
        select count(1) from fruit fsq where
            f1.fruit = fsq.fruit and
            f2.ripeness = fsq.ripeness
    ) from fruit f1
    left join fruit f2 on true
    group by f1.fruit, f2.ripeness
  )
  union
  (
  select fruit.fruit, null, count(1) from fruit
    group by fruit.fruit
  )
  union
  (
  select null, null, count(1) from fruit
  )
) mq
order by 1, 2;

Demo.

user_129142740
Related