CODEWITHSUNDEEP
mysqlsql
user2531590
user2531590

Reputation:

SQL statement Max(Count(*))

Basically I have a review table for product. The attributes are reviewID, reviewCustName, reviewText, productID. So I wonder is there any ways to count the product with most reviews? Here is my SQL statement:

SELECT productID, count(*) AS mostReviews, MAX(mostReviews) FROM sm_review GROUP BY productID;

I wonder is it possible to write such SQL statement? Or i there any better way?

Thanks in advance.

Upvotes: 0

Views: 163

Answers (2)

apartridge
apartridge

Reputation: 1820

It should just be; 

SELECT count(*) AS num_reviews FROM sm_review 
GROUP BY productID ORDER BY num_reviews DESC LIMIT 1;

Note the ORDER BY num_reviews and the LIMIT 1 which limits the number of results.

Upvotes: 1

Taryn
Taryn

Reputation: 247870

You can use the following to get the result. This gets the total count for each product but when you order the count in a descending order and apply LIMIT 1 it returns only the product with the most reviews:

select count(*) total
from sm_review
group by productId
order by total desc
limit 1

Upvotes: 2

Related Questions