1

I have a SQL query to count the number of rating with at least 15 records and then order them by average rating.

So here is what I came up with:

SELECT name, COUNT(rate) FROM RateTable
WHERE rating = 5
GROUP BY name
HAVING COUNT(rate) > 15
ORDER BY AVG(rate) ASC;

It did actually filtered those product with less than 15 records, however the results is not sorted in order. Any ideas?

Thanks in advance.

EDIT

name Count(rate) AVG(rate) product1 20 5 product2 18 5 product3 17 5

Who am I
  • 33
  • 5
  • I see it's pretty standard SQL, but however you'd better tell us what RDBMS you are working on. A minimal, simple, complete example would also help. – Insac Oct 22 '16 at 07:47
  • At first glance I don't see anything wrong in this query. Can you show us the actual outcome? – Insac Oct 22 '16 at 07:50
  • Sorry as I am quite new so I am forbidden to post image. My results are: product1 20, product2 15, product3 18, product4 17. I am using MSSQL – Who am I Oct 22 '16 at 07:54
  • It's better you don't post images. You can take this data (formatted) and add it to the original question. – Insac Oct 22 '16 at 07:55
  • Ok.. But what you show is the count, not the average. How do you know it's not the correct order? – Insac Oct 22 '16 at 07:56
  • You'd better show in the output also the average rate and see if the result is OK for you. – Insac Oct 22 '16 at 07:59
  • Okay sorry my concept might be wrong. So what I need to do is first count if their rating records are > 15 rows and then sort them by their average rating. I printed out the AVG and all I get is 5 only. Is that supposed to be that way? – Who am I Oct 22 '16 at 08:00
  • @Insac Any ideas? – Who am I Oct 22 '16 at 08:17
  • Show some data of the RateTable (name, rate, rating) and the query you're now testing. It sounds like rate=rating. – Insac Oct 22 '16 at 08:45
  • @Insac Hello, after hours of trying and I can't still figure out what went wrong. I have updated my answer. Hope you can look into it. But just to check with you because I am retrieving those rate which is 5 only, so even if I AVG it, no matter how much records I have for that specific product, it will still return 5 right? because that's what I get – Who am I Oct 22 '16 at 10:48
  • I thought initially that rating and rate were two different fields. If they have always equal value, then yes, the average rate of all the records with rate=5 will be 5. In this case the order is meaningless. – Insac Oct 22 '16 at 11:13
  • I see. That solves my problem. I think it is a tricky question. Thanks! – Who am I Oct 22 '16 at 11:15

2 Answers2

1
SELECT * FROM 
(
    SELECT name, COUNT(rate), AVG(rate) as Rate FROM RateTable
    WHERE rating = 5
    GROUP BY name
    HAVING COUNT(rate) > 15
) as Result ORDER BY Rate ASC
Beginner
  • 4,118
  • 3
  • 17
  • 26
0

May be this is due to the improper use of aggregate function in ORDER BY clause, there are certain limitations. The following link may help you... What can an aggregate function do in the ORDER BY clause?

Best of luck.

Community
  • 1
  • 1
Shubham Kundu
  • 185
  • 1
  • 10