I need to get a set of results showing the number of items accumulated for each 'esta' group.
I'm grouping the results by establishment.
Establishment is inner joined to base.
Left joined items are joined against the base.
So in Esta group 2, let's say there are 3 base ids. Each written and verbal record attached to the base ID would count towards that esta in the results set. There can be multiple 'written' or 'verbal' attached to each base record.
I have 6 verbals and 4 writtens in the database, they are spread around the different 'esta' records. In my query, they are all counting towards the first row of the result I get.
I have tried the same with much more data, and regardless of the 'esta', the first row contains every left joined element counted together.
,SUM(IF(verbal.enf_verbal_id is not null,1,0)) as verbals
,SUM(IF(written.enf_written_id is not null,1,0)) as writtens
INNER JOIN enf_esta esta ON esta.enf_esta_id = base.enf_esta_id
LEFT JOIN enf_verbal verbal ON verbal.enf_base_id = base.enf_base_id
LEFT JOIN enf_written written ON written.enf_base_id = base.enf_base_id
enf_esta_id verbals writtens
2 10 10
3 1 0
4 1 1
6 0 0
COUNT( * ) AS total
INNER JOIN enf_base ON enf_base.enf_base_id = enf_written.enf_base_id
INNER JOIN enf_esta ON enf_base.enf_esta_id = enf_esta.enf_esta_id
You are multiplying. Say there are 2 verbals and 5 writtens then your joins make these 10 records (i.e. all combinations). Rather then joining tables and aggregating then, you should first aggregate and then join your aggregates. In your case this is aggregates per base ID, which you will finally further aggregate to get estas.
select base.enf_esta_id, coalesce(sum(verbal.cnt), 0) as verbals, coalesce(sum(written.cnt), 0) as writtens from enf_base base left join ( select enf_base_id, count(*) as cnt from enf_verbal group by enf_base_id ) verbal on verbal.enf_base_id = base.enf_base_id left join ( select enf_base_id, count(*) as cnt from enf_written group by enf_base_id ) written on written.enf_base_id = base.enf_base_id group by base.enf_esta_id;