complex query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

complex query problem

Post 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?
marcth
Forum Contributor
Posts: 142
Joined: Mon Aug 25, 2008 8:16 am

Re: complex query problem

Post 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?
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

Re: complex query problem

Post by recci »

yeah it is
Post Reply