How to return highest SUM values using JOINS and GROUP BY clause

[holdench]

I've got two important tables.

One has a list of questions within categories...
One has a rating for questions the user chose to give points to...

Columns in table1 (questions) are:
id
question
category_id

Columns in Table2 (rating) are:
id
q_id
rating
user_ip
time
c_id

The query i am using is :

SELECT * FROM questions as q,(SELECT SUM(rating) as totq FROM rating WHERE c_id='category_id') r,(SELECT SUM(rating) AS sumr FROM rating WHERE 'q_id=q.id') ra WHERE category_id='".$rowcat[0]."' ORDER BY (ra.sumr/r.totq),id
 

What I am trying to get is a list of questions sorted by the highest ranked (most points out of overall CATEGORY points) to lowest ranked questions. also separated by category headings.

So say I have three categories ... they seperate the sections like this

Cat1
que1
que2
que3

Cat2
que1
que2
que3

On the results page, if say the people voted and question3 was ranked highest in category 1 and question 2 in category 2. the output i am looking for is

Cat1
que3
que1
que2

Cat2
que2
que3
que1

Please let me know if my select statement is wayyy off, this is killing me. I have never done such a complicated select before.

Thanks!
Chad

[Atli]

I'm not really sure how all your fields work, but I imagine you could do something like this:

SELECT
  q.question,
  SUM(r.rating) / COUNT(r.rating) AS 'Rating'
FROM questions AS q
INNER JOIN  rating AS r
  ON q.id = r.q_id
WHERE q.category_id = 1
GROUP BY q.id
ORDER BY SUM(r.rating) / COUNT(r.rating) DESC
LIMIT 3;
 

This will give you the three highest ranking questions from a given category_id, returning the questions and the avarage rating for each question in order from highest ranking to lowest ranking.

[Atli]

I have edited the thread's title to better describe it's contents. (I hope)
Please read the Posting Guidlines before posting

Moderator