Reputation: 7278
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
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