CODEWITHSUNDEEP
sqlms-access
Екатерина Самойлова
Екатерина Самойлова

Reputation: 5

How to get Max from Count in SQL (Access)?

There is a table called "Athletes" which has columns for "Group" and "Award". It is necessary to calculate using Count the number of awards in groups separately and display the group with the maximum number of awards and the number of awards for this group. I tried to make a request of this type:

SELECT Max (Reward1) AS Reward 
FROM (
     SELECT Count (Athletes.Reward) AS Reward1 
     FROM Athletes 
     GROUP BY Athletes.Group
) AS [% $ ## @ _ Alias];

This works, but the column corresponding to the maximum number of awards is not displayed for the group that received these awards. Can you please tell me what is worth adding so that this column is also displayed?

Upvotes: 0

Views: 75

Answers (1)

Tyler Morris
Tyler Morris

Reputation: 21

You're not selecting the GROUP column. You don't need to nest the query in a subquery, you can just pull the largest count of athlete rewards by selecting the largest row from your query.

SELECT TOP 1 Athletes.Group as Group
    , Count (Athletes.Reward) AS Reward1 
FROM Athletes 
GROUP BY Athletes.Group
ORDER BY Reward1 DESC;

Upvotes: 1

Related Questions