Case statement to find medal tally for each country-CodePudding

I have written the following query in SQL Server in order to find the medal tally for the countries based on the count of athletes winning a medal

SELECT 
    SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS 'Gold',
    SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS 'Silver',
    SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS 'Bronze', 
    COUNT(*) AS total_medals, 
    TEAM
FROM 
    [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]
GROUP BY 
    TEAM
ORDER BY 
    COUNT(*) DESC

Since for team sports such as hockey, each individual player's medal is counting towards the tally, the count is becoming much higher than it is actually supposed to be. Is there a way I could modify the case statement or use a CTE or Window function to count a medal only once for a single event in case of a team sport.

The table I am using has 6 columns

ATHLETE_NAME, TEAM (country), SPORT, EVENT, MEDAL, CONTINENT

CodePudding user response：

You could use a CTE (or derived table) to get the DISTINCT rows, and then aggregate:

WITH CTE AS(
    SELECT DISTINCT
           TEAM,
           SPORT,
           EVENT,
           MEDAL
    FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]) --I really suggest a better object name
SELECT SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold, --Don't use literal strings for aliases
       SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,  --Don't use literal strings for aliases
       SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze, --Don't use literal strings for aliases
       COUNT(*) AS total_medals,
       TEAM
FROM CTE
GROUP BY TEAM
ORDER BY COUNT(*) DESC;

Alternatively, you could get the TOP 1 per group and aggregate:

WITH CTE AS
    (SELECT TEAM,
            MEDAL,
            ROW_NUMBER() OVER (PARTITION BY TEAM, SPORT, EVENT, MEDAL ORDER BY ATHLETE_NAME) AS RN
     FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]) --I really suggest a better object name
SELECT SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold,
       SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,
       SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze,
       COUNT(*) AS total_medals,
       TEAM
FROM CTE
WHERE RN = 1
GROUP BY TEAM
ORDER BY COUNT(*) DESC;