CODEWITHSUNDEEP
mysqlsortingmaxcategories
Ashish Singh
Ashish Singh

Reputation: 135

Counting results in MySQL with highest value by category

Here is my table

http://www.sqlfiddle.com/#!2/47e2d/8

╔════╦══════════╦════════╦═══════╦════════╗
║ id ║ category ║ userid ║ users ║ points ║
╠════╬══════════╬════════╬═══════╬════════╣
║ 1  ║    2     ║  91    ║ user1 ║  100   ║
║ 2  ║    3     ║  92    ║ user2 ║   80   ║
║ 3  ║    4     ║  93    ║ user3 ║   10   ║
║ 4  ║    4     ║  91    ║ user1 ║   12   ║
╚════╩══════════╩════════╩═══════╩════════╝

How do I get the count result of users with the highest points per category?

In the above example, I want to get

user1: 2
user2: 1

After searching for similar problems in stackoverflow for some time, I came up with 

SELECT users, COUNT(*) 
FROM `table` 
WHERE points=(SELECT MAX(points) FROM `table`) 
GROUP BY points;

But it does not produce what I want.

Upvotes: 3

Views: 1195

Answers (3)

Andy Jones
Andy Jones

Reputation: 6275

Try this:

SELECT users, count(*) 
FROM `TABLE`, 
  (SELECT category, max(points) as max_points FROM `TABLE` GROUP BY category) as maxes
WHERE maxes.category = `TABLE`.category AND maxes.max_points = `TABLE`.points
GROUP BY users

The key here is creating a temporary table that contains the max points for each category

SELECT category, max(points) as max_points FROM `TABLE` GROUP BY category

Which I then use to join with the existing TABLE to find the correct user who has the maximum points.

Upvotes: 2

Leo Zhao
Leo Zhao

Reputation: 544

Try this:

  SELECT 
  users,
  COUNT(*) 
FROM
  (SELECT 
    users,
    COUNT(*) COUNT
  FROM
    `table` 
  WHERE points IN 
    (SELECT 
      MAX(points) 
    FROM
      `table` 
    GROUP BY category) 
  GROUP BY points) temp 
GROUP BY users ;

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT
  t1.users,
  COUNT(t2.category)
FROM `table` AS t1
INNER JOIN
(
  SELECT category, MAX(points) MAxpoints
  FROM `table`
  GROUP BY category
) AS t2 ON t1.category = t2.category AND t1.points = t2.MAxpoints
GROUP BY t1.users;

The joined subquery:

  SELECT category, MAX(points) MAxpoints
  FROM `table`
  GROUP BY category

will give you the max points for each category, then we get those users of those max points, by joining that subquery with the orginal table, then we use the COUNT to count those categories of the max points for each user.

This will give you:

╔════════╦═══════╗
║ USERS  ║ COUNT ║
╠════════╬═══════╣
║ user1  ║     2 ║
║ user2  ║     1 ║
╚════════╩═══════╝

Upvotes: 2

Related Questions