Posted this is the php forum but it makes more sense in here!
I have the following 2 tables
+--------+--------+-----+
| pic_id | rating | uid |
+--------+--------+-----+
| 1 | 4 | 1 |
| 2 | 3 | 1 |
| 3 | 6 | 2 |
| 4 | 2 | 1 |
| 5 | 1 | 2 |
+--------+--------+-----+
+------------+--------+-----+
| comment_id | rating | uid |
+------------+--------+-----+
| 1 | 1 | 2 |
| 2 | 3 | 1 |
| 3 | 4 | 3 |
| 4 | 3 | 2 |
+------------+--------+-----+
What I want to do is get an overall score for a user based on all their ratings (so assume comment_id 1 belongs to uid 2 as well as pic_id 3) Both 'entities' belong to the user and the rating is a sum already calculated by a trigger. i.e. it's an overall rating for that pic/comment based on other user ratings.
What I want to do is get an overall score for the user based on how people have rated all their pics and comments. There is the possibilty that the user might have left comments but not have any pictures hence the right join.
I've tried like so :
select
sum(p.rating) as prating,
sum(c.rating) as crating ,
(sum(c.rating) + sum(p.rating)) as orating ,
p.uid as puid,
c.uid as cuid
from pic_rating as p
right join comment_rating as c
on p.uid = c.uid
group by p.uid, c.uid
order by orating desc
But this produces the following, incorrect results:
prating 14 crating 8 uid is 2 and orating is 22
--expected prating 7, crating 4, orating 11
prating 9 crating 9 uid is 1 and orating is 18
--expected prating 9, crating 3 orating 12
prating crating 4 uid is 3 and orating is 4
--expected prating 0, crating 4, orating 4
Can anybody see what I am doing wrong here? Or suggest an alternative way to do this? I don't really want to run a second query if I can avoid it, but if that's my only option and then performing the addition in php I guess that's what I'll have to do
Cheers
