select b.id2 ,max(date) ,sum(case when b.oyako=1 then 1 else 0 end) as oya ,sum(case when b.oyako=2 then 1 else 0 end) as kofrom table1 a left outer join table2 b on a.id = b.id2group by b.id2