Select the number of English players from team which scored the most goals at home on a match-CodePudding

dbrelation

I tried to calculate the number of English players from that team which scored the most goals on a match at home, from the database above (SQL Server).

My query:

SELECT TOP 1 COUNT(PLAYER.PLAYER_ID), MAX(HOME_SCORE)
FROM PLAYER 
JOIN NATION ON PLAYER.NATION_ID = NATION.NATION_ID
JOIN MATCH ON MATCH.HOME_TEAM_ID = PLAYER.TEAM_ID
WHERE NATION.NATION_NAME = 'England'
GROUP BY PLAYER.PLAYER_ID
ORDER BY MAX(MATCH.HOME_SCORE) DESC

It gives correct answer for the max(home_score), but the count of English players isn't valid.

CodePudding user response：

Just an untested notepad scribble.
Since I can't verify it without sample data.

SELECT TOP 1
  match.match_id
, match.home_score
, COUNT(CASE 
        WHEN player_nation.nation_name = 'England'
        THEN player.player_id
        END) AS EnglishPlayers
, COUNT(goal.goal_id) AS TotalGoals
FROM GOAL AS goal
JOIN PLAYER AS player 
  ON player.player_id = goal.player_id
JOIN MATCH AS match 
  ON match.match_id = goal.match_id
 AND match.home_team_id = player.team_id
LEFT JOIN NATION AS player_nation 
  ON player_nation.nation_id = player.nation_id
GROUP BY match.match_id
ORDER BY TotalGoals DESC

CodePudding user response：

You need to left-join PLAYER to MATCH. You can nest the join to NATION inside it.

You are also grouping by the wrong value

SELECT TOP (1)
  p.TEAM_ID,
  NumPlayers = COUNT(*),
  NumEnglishPlayers = COUNT(p.PLAYER_ID),
  HighestScore = MAX(m.HOME_SCORE) 
FROM MATCH m
LEFT JOIN PLAYER p
    JOIN NATION n ON p.NATION_ID = n.NATION_ID
        AND n.NATION_NAME = 'England'
  ON m.HOME_TEAM_ID = p.TEAM_ID
GROUP BY
  p.TEAM_ID
ORDER BY
  HighestScore DESC;