sUP sUP - 3 months ago 12
MySQL Question

Count one-to-many using left join gives wrong count on source table

I have two tables

user_results
and
user_shares


user_results
has:

id - pk
platform - string
country - string
created_at - timestamp


user_shares
has:

id - pk
user_result_id - integer
created_at - timestamp


This is my query:

select
count(user_results.id) as results,
count(user_shares.id) as shares,
user_results.platform as platform
from
user_results
left join
user_shares on user_results.id = user_shares.user_result_id
group by
platform


Here's a sql fiddle with the schema and problematic query:
http://sqlfiddle.com/#!9/739a3/2

Now, as you can see on the fiddle I have only 1 result per platform but the
results
count shows (i guess) the "results count without share + shares count"

What do I do wrong?

Answer

What you need is to count distinct user_results.id, because joined table duplicates its rows:

select 
  count(distinct user_results.id) as results,
  count(user_shares.id) as shares,
  user_results.platform as platform 
from
  user_results
left join 
  user_shares on user_results.id = user_shares.user_result_id 
group by 
  platform