Three tables with two counts with group by-CodePudding

In my query I want total file count and total closed files in the same table.

My first query:

select hi.eksper_id,ef.ad, count(hi.eksper_id) as total_files
from hasar_ihbar as hi 
left outer join eksper_firma ef on ef.id=hi.eksper_id
group by hi.eksper_id,ef.ad

My second query:

select ef.id as eksper_id,ef.ad,count(ef.id) closed_files
from hasar_ihbar_rapor hir
left outer join hasar_ihbar hi on hi.id = hir.hasar_ihbar_id
left outer join eksper_firma ef on ef.id = hi.eksper_id
where hir.rapor_tipi = 3 group by ef.id,ef.ad

I want both combined and this is my code:

select ef.id as eksper_id,ef.ad,count(ef.id) closed_files, count(hi.id) AS total_files
from hasar_ihbar_rapor hir
left outer join hasar_ihbar hi on hi.id = hir.hasar_ihbar_id
left outer join eksper_firma ef on ef.id = hi.eksper_id
where hir.rapor_tipi = 3 group by ef.id,ef.ad

I don't know what I did wrong. Thanks for your help.

CodePudding user response：

The double joins effect your counts since there are more rows. A better way to combine both counts is to create a query that has two subqueries for each count.

SELECT id, 
    (SELECT COUNT(*)...) total_files,
    (SELECT COUNT(*)...) closed_files
FROM ...