CODEWITHSUNDEEP
mysql
swap
swap

Reputation: 5

count highest occurance category-wise in sql select statement

I have a table from which i want to COUNT highest no of occurrence category-wise and output that specific entry only.

I tried this query but no luck $sql=" SELECT category,article,COUNT(*) FROM articles_likes WHERE category = '{$category}' GROUP BY article DESC";

for eg:

table - 

article category
1         A
1         B
1         B
2         A
2         A
2         B
3         A
4         B

Expected output -

If I select A then it should be 2

If I select B then it should be 1

Upvotes: 0

Views: 1240

Answers (1)

Andrew Jones
Andrew Jones

Reputation: 1382

You want to sort the articles in descending order of their counts for a category. Limiting the query to a single record will give you the article with the most counts. For A, the following query will work. Note that I have used table as the table name because the question didn't provide the table name.

SELECT article
FROM table
WHERE category = 'A'
GROUP BY article
ORDER BY COUNT(*) DESC
LIMIT 1

If you need more information than just the article, then you can add the following to your select statement for more details.

SELECT article, category, COUNT(*)
FROM table
WHERE category = 'A'
GROUP BY article
ORDER BY COUNT(*) DESC
LIMIT 1

Upvotes: 2

Related Questions