Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv','stat',89)
)
select t.id::text, t.other_column1, t.other_column2, t.amount from t
union all
select 'Total', null, null, sum(amount) from t


id
other_column1
other_column2
amount


9
some serv
stat
100

10
some serv
stat
89

Total
null
null
189



SELECT 3


with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv','stat',89)
)
select case when t.id is null then 'Total' else t.id::text end as id
     , t.other_column1
     , t.other_column2
     , case when t.id is null then ext.sum else t.amount end as amount
from (
  select t.id, sum(amount) as sum
  from t
  group by rollup(t.id)
) ext
left join t on ext.id = t.id
order by ext.id


id
other_column1
other_column2
amount


9
some serv
stat
100

10
some serv
stat
89

Total
null
null
189



SELECT 3


with t(id,other_column1,other_column2,amount) as (values
  (9,'some serv','stat',100),
  (10,'some serv2','stat',89)
)
select case grouping(t.id) when 0 then t.id::text else 'Total' end as id
     , case grouping(t.id) when 0 then t.other_column1 end as other_column1
     , case grouping(t.id) when 0 then t.other_column2 end as other_column2
     , sum(t.amount) as amount
from t
group by grouping sets((t.id, t.other_column1, t.other_column2), ())
order by t.id


id
other_column1
other_column2
amount


9
some serv
stat
100

10
some serv2
stat
89

Total
null
null
189



SELECT 3