2

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;
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.