Reputation: 5
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
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