PostgreSQL 14

Assuming this sample data:

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:

Instead of only the existing values in the group appearing like this:

CodePudding user response：

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.

CodePudding user response：

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.