CODEWITHSUNDEEP
mysqlsqlcount
disasterkid
disasterkid

Reputation: 7278

Get counts of rows for each joined table for each row in the main table

I have three tables in my database. The central table in the star schema is sendings with a PK column called id. Each sending record can have zero or more rows in the tables opens. 

sendings.id = opens.sending_id

Similarly, a third table called clicks have the following association to the sending table (one sending can have zero or more clicks):

sendings.id = clicks.sending_id

Both opens and clicks tables have their unique id column, called id. What I would like to have in one query, is the count of all associated opens and clicks for each sending. The following query does not seem to meet that demand.

select s.id,
    count(o.id) as open_count,
    count(c.id) as click_count
from sendings s
left join opens o on s.sending_id = o.sending_id
left join clicks c on s.sending_id = c.sending_id
group by s.id;

Upvotes: 0

Views: 18

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The simple solution is to use count(distinct):

select s.id,
       count(distinct o.id) as open_count,
       count(distinct c.id) as click_count
from sendings s left join
     opens o
     on s.sending_id = o.sending_id left join
     clicks c
     on s.sending_id = c.sending_id
group by s.id;

count() just counts the number of non-NULL values.

In general, a more performance solution is either correlated subqueries or aggregation before the join:

select s.id, o.open_count, c.click_count
from sendings s left join
     (select o.sending_id, count(*) as open_count
      from opens o
      group by o.sending_id
     ) o
     on s.sending_id = o.sending_id left join
     (select c.sending_id, count(*) as click_count
      from clicks c
      group by c.sending_id
     ) c
     on s.sending_id = c.sending_id;

Note that the outer group by is not necessary in this case.

Upvotes: 2

Related Questions