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 tb as(
select 'a_subsec1' sec,'comp' inv
union all
select 'a_subsec1' sec,'comp' inv
union all
select 'a_subsec2' sec,'lap' inv
union all
select 'a_subsec2' sec,'comp' inv
union all
select 'a_subsec3' sec,'lap' inv
union all
select 'a_subsec3' sec,'comp' inv
)
select msec,sum(inv_comp) total from(
select concat(substr(sec,1,1),'_sec') as msec,
case when inv='comp' then 1 else 0 end as inv_comp,
tb.*
from tb) z
group by msec
| msec | total |
|---|---|
| a_sec | 4 |