Postgres 12

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?.

create table Table1 (id INT,group_id INT, amount INT, size VARCHAR)

CREATE TABLE

create table Table2 (id INT,group_id INT, amount INT)

CREATE TABLE

insert into Table1 values (1,1,10, 'L');

INSERT 0 1

insert into Table1 values (2,2,20, 'M');

INSERT 0 1

insert into Table1 values (3,2,80, 'M');

INSERT 0 1

insert into Table2 values (1,1,10);

INSERT 0 1

insert into Table2 values (2,2,50);

INSERT 0 1

insert into Table2 values (3,2,60);

INSERT 0 1

SELECT t1.GROUP_ID, sum(t1.AMOUNT), sum(t2.amount)
from Table1 t1
left join Table2 t2
on t1.id = t2.id and t1.GROUP_ID = t2.GROUP_ID
group by t1.GROUP_ID
order by t1.GROUP_ID;

group_id
sum
sum

1
10
10

2
100
110

SELECT 2

select a.group_id,a.amt as tableamnt,b.amt as table2amt from (
select group_id,sum(amount) as amt from table1
) a join (    
select group_id,sum(amount) as amt from table2
) b on a.group_id=b.group_id

ERROR:  column "table1.group_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: select group_id,sum(amount) as amt from table1
               ^