Search code examples
sqlsql-serveraggregate-functions

How to aggregate in 1 table

I have an simplified example of my data:

Soccer team Player Goals
Man. City Haaland 3
Man. City De Bruyne 0
Man. City Foden 0
Man. Utd Hojlund 0
Man. Utd Onana 0
Man. Utd Maguire 0

And now I want to add a column that checks if a team has any goalscorers. So my result should be:

Soccer team Player Goals Goals in team
Man. City Haaland 3 y
Man. City De Bruyne 0 y
Man. City Foden 0 y
Man. Utd Hojlund 0 n
Man. Utd Onana 0 n
Man. Utd Maguire 0 n

How could I achieve this? Which SQL statement should i use. Remember I have like 25 other columns. Should I still put it in an aggregation function and group by all of the other columns, or are there easier ways?

Solution

  • You can use SUM() with CASE statement.

    SELECT SoccerTeam, Player, Goals,
    CASE WHEN SUM(Goals) OVER (PARTITION BY SoccerTeam) > 0 THEN 'y' ELSE 'n' END AS GoalsInTeam
FROM YourTable;