MySQL Join – How to Select from 3 Tables with Count and Sum

join;MySQL

I'm stuck on this one query. Here is my setup:

table 1 (shows)

  • show_user
  • show_count

table 2 (views)

  • view_user
  • view_views

table 3 (users)

  • user
  • user_name

What I need to is produce results that contain:

sum(show_count), count(view_views), user_name

So far I have tried the following:

select sum(s.show_count), count(v.view_views), u.user_name 
from users u
left join shows s on u.user = s.show_user
left join views v on u.user = v.show_user
group by u.user

The query is taking an eternity to run and returns neither the right count nor the sum.

Individually, the following queries work properly:

select sum(show_count), show_user from shows group by show_user;
select count(view_view), view_user from views group by view_user;

Best Answer

I assume you have multiple entries in shows, views table for each user. When joining all three together, you get more rows than you expect: if for some user you have x rows in table shows and y rows in table views, then the result set contains x*y rows for that user, so you sum each entry in shows y times, and each entry in views table gets counted x times.

You need to aggregate each table separately, and then join the results:

select t1.sum1, t2.cnt, u.user_name
from users u
left join (select show_user, sum(show_count) sum1 from shows group by show_user) t1 on u.user = t1.show_user
left join (select view_user, count(view_views) cnt from views group by view_user) t2 on u.user = t2.view_user

SQLFiddle

Related Question