Page 1 of 1

complex query problem

Posted: Tue Sep 23, 2008 5:34 pm
by recci
Ok I have this and it works:

CREATE TABLE test_ratings (user_id INT,title_id INT,rating INT, PRIMARY KEY (user_id,title_id));

INSERT INTO test_ratings VALUES (1,1,4),(1,2,3),(1,3,2),(1,4,1);

The following query would give the number of points each user had allocated to each title, expressed as a percentage of the total number of points allocated by each user:

SELECT t1.user_id, t1.title_id, t1.rating,SUM(t2.rating) total,(t1.rating/SUM(t2.rating))*100 pct
FROM test_ratings t1
JOIN test_ratings t2
ON t1.user_id = t2.user_id
GROUP BY t1.user_id,t1.title_id;

but I want limit it to a single user_id, Iv tried this but it does not work, getting a error msg

SELECT t1.user_id, t1.title_id, t1.votes,SUM(t2.votes) total,(t1.votes/SUM(t2.votes))*100 pct
FROM jos_comprofiler_plug_funratings t1 WHERE t1.user_id = 79
JOIN jos_comprofiler_plug_funratings t2
ON t1.user_id = t2.user_id
GROUP BY t1.user_id,t1.title_id;

What am I doing wrong here?

Re: complex query problem

Posted: Tue Sep 23, 2008 5:49 pm
by marcth
I haven't used mysql in many years, so forgive me if I misunderstood your SQL? Is you table making use of a compound primary key?

Re: complex query problem

Posted: Tue Sep 23, 2008 6:10 pm
by recci
yeah it is