CODEWITHSUNDEEP
mysql
kudlajz
kudlajz

Reputation: 1066

MySQL query is not working as expected

I have problem with a MySQL query with joins, counting and using sum. Environment is available here: http://sqlfiddle.com/#!2/38e813/5

My goal is to select all of user's data, count number of comments and posts he has and sum up all ratings which has he received. Altough as you can see from the example, even though there are 3 records of rating, final rating number returned is -4 (should be -3). I assume it will need some work with group by?

Upvotes: 1

Views: 68

Answers (1)

Andy Refuerzo
Andy Refuerzo

Reputation: 3332

Here my answer using derived tables (as x and as y):

select x.id, x.active, x.ip, x.registered, x.username, x.email, x.role, 
   x.posts, y.comments, x.rating + y.rating as rating 
from
   (select u.id, u.active, u.ip, u.registered, u.username, u.email, u.role, 
    count(p.user_id) as posts, ifnull(sum(pr.rating),0) as rating 
    from users u 
    join posts p on p.user_id=u.id
    left join posts_ratings pr on pr.post_id=p.id) as x
join
   (select u.id, u.active, u.ip, u.registered, u.username, u.email, u.role, 
    count(c.user_id) as comments, ifnull(sum(cr.rating),0) as rating 
    from users u 
    join comments c on c.user_id=u.id
    left join comments_ratings cr on cr.comment_id=c.id) as y
on x.username=y.username;

The x table gets the number of posts and total rating while the y table gets the number of comments and total rating. Both tables are joined by username (or user_id if you want) and both ratings from each table are added together.

To get all the user's data, you must explicitly list the columns from the users table in both the x and y tables:
select u.id, u.active, u.ip, u.registered, u.username, u.email, u.role, ... COLS here
then do the same in the outmost select (first line):
select x.id, x.active, x.ip, x.registered, x.username, x.email, x.role, ... COLS here

To get a specific user, add WHERE clause on table x, table y and the outmost select.

Upvotes: 2

Related Questions