complex query problem
Posted: Tue Sep 23, 2008 5:34 pm
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?
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?