Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl1 (
  id int,	
  name text,	
  call int );

insert into tbl1 values 
(123,'aaaaaa',15),
(132,'bbbbb', 0)
returning *;

create table tbl2 (
  id int,	
  involvement int);

insert into tbl2 values 
(123,0),
(123,0),
(123,1)
returning *;

CREATE TABLE




id
name
call


123
aaaaaa
15

132
bbbbb
0



INSERT 0 2



CREATE TABLE




id
involvement


123
0

123
0

123
1



INSERT 0 3


--OP's attempt
select t1.id,
       COUNT(CASE WHEN t1.call=15 THEN 1 END) as calls
from Tbl1 t1 
  left join Tbl2 t2 
    on t2.id = t1.id 
where t2.involvement = 0;

ERROR:  column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: select t1.id,
               ^


--OP's attempt, corrected
select t1.id,
       COUNT(CASE WHEN t1.call=15 THEN 1 END) as calls
from Tbl1 t1 
  left join Tbl2 t2 
    on t2.id = t1.id 
where t2.involvement = 0
group by t1.id;


id
calls


123
2



SELECT 1


select COUNT(distinct t2.id)filter(where t1.call=15) as calls,
       array_agg(distinct t2.id)filter(where t1.call=15) as the_ids
from Tbl1 t1 
  left join Tbl2 t2 
    on t2.id = t1.id 
where t2.involvement = 0;


calls
the_ids


1
{123}



SELECT 1