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 ratins (so assume comment 1 belongs to uid 2 as well as pic3) The object belong to the user and the rating is a sum already calculated by a trigger.
What I want to do is get an overall score for the user based on how people have rated all their pics or comments. 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
