SQL order by: question

  • Travis Coats
  • Graduate
  • Graduate
  • Travis Coats
  • Posts: 107

Post 3+ Months Ago

I have a ratings system set up to group tracks by rating and then order them by the avg_rating. Problem is that I need to go a bit further and count the number of times the track was rated and then order the output by that number. Here's my SQL statement now which addresses a table that has two columns: track and rating

Code: [ Select ]
SELECT track, ROUND( avg( rating ) , 1 ) as avg_rating
FROM `trackRatings`
GROUP BY track
ORDER BY avg_rating DESC
  1. SELECT track, ROUND( avg( rating ) , 1 ) as avg_rating
  2. FROM `trackRatings`
  3. GROUP BY track
  4. ORDER BY avg_rating DESC


I have read a few tutorials that seem to mention using the count command, but I don't know the first thing about implementing it without screwing up what I have. :)

I am assuming I need to append a little something here:

Code: [ Select ]
ORDER BY avg_rating DESC AND (something)
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Well COUNT is what's called an aggregate function just like AVG is. Most times when you're already using one aggregate function in a query, another will be simple to add.

In your case it looks like your addition will be along the lines of

Code: [ Select ]
, COUNT(rating) AS total_ratings


Which will in turn give you the query

Code: [ Select ]
SELECT
    track,
    ROUND(AVG(rating), 1) AS avg_rating,
    COUNT(rating) AS total_ratings
FROM
    trackRatings
GROUP BY
    track
ORDER BY
    avg_rating DESC,
    total_ratings DESC
LIMIT
    10
  1. SELECT
  2.     track,
  3.     ROUND(AVG(rating), 1) AS avg_rating,
  4.     COUNT(rating) AS total_ratings
  5. FROM
  6.     trackRatings
  7. GROUP BY
  8.     track
  9. ORDER BY
  10.     avg_rating DESC,
  11.     total_ratings DESC
  12. LIMIT
  13.     10


And since it shows up atop search results for dealing with multiple column sorts anyway, here's a post about sorting by multiple columns in MySQL I made awhile ago. :)
  • Travis Coats
  • Graduate
  • Graduate
  • Travis Coats
  • Posts: 107

Post 3+ Months Ago

Seems to be working correctly. At first I was wondering if it was, but I sat down and did the math manually and it works like a charm. Thanks a lot!
  • sandy_j_sam
  • Newbie
  • Newbie
  • sandy_j_sam
  • Posts: 9

Post 3+ Months Ago

hi, I agree with that joebert, but I just want to start from basic

(1)SELECT * form table name; => we use that sql for retrieving data from data base.
(2)AVG => Multiple row Function
(3)COUNT => Refer No. of record in database
GROUP BY, which is used for grouping data like

table_record
designation : salary
Supplier 10,000
Supplier 10,000
Supplier 10,000
Owner 70,000

SELECT designation, avg(salary) from table_record GROUP BY(designation);

means to say that table becomes
table_record
designation : salary
Supplier 10,000
Owner 70,000


(4)ORDER BY, either in ASE or DESC order, the record of table.
(5)ROUND => Single row Function

HI, you just read them for clearing basic Concept.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 102 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.