CODEWITHSUNDEEP
mysqlsqljoingroup-by
blodey
blodey

Reputation: 75

Count of joined items per group in MySql

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.

sql:

SELECT 
     esta.enf_esta_id
     ,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
FROM 
     enf_base base
          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
WHERE 
     1=1
GROUP BY 
     esta.enf_esta_id

result:

enf_esta_id verbals writtens
2           10      10
3           1       0
4           1       1
6           0       0

To prove that the top row is incorrect, here are the results of just getting the verbals and writtens from enf_esta_id 2.

SELECT 
     COUNT( * ) AS total
FROM 
     enf_written
         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
WHERE 
     enf_esta.enf_esta_id =2

yields:

5

And the same with enf_verbal yields 2. adding up the totals of each gives us the correct 10 if we discount the top row of the problem query result.

Can anyone help me get the result I need?

Upvotes: 3

Views: 55

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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;

Upvotes: 1

Related Questions