SQL Server How to aggregate in 1 table

wvyml7n5  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(117)

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 teamPlayerGoalsGoals in team
Man. CityHaaland3y
Man. CityDe Bruyne0y
Man. CityFoden0y
Man. UtdHojlund0n
Man. UtdOnana0n
Man. UtdMaguire0n

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?

q9rjltbz

q9rjltbz1#

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;
u0njafvf

u0njafvf2#

You can use a window function inside a CASE expression for this. I've put * in the query because you said you had lots of other columns. You should replace the star with the list of columns you want in the query. You can also put columns after the 'goals_in_team' column if you prefer.

SELECT  *, --replace with your list of columns in preferred order
        CASE 
            WHEN SUM(goals) OVER (PARTITION BY soccer_team) > 0 
            THEN 'y' 
            ELSE 'n' 
        END AS goals_in_team
FROM results;

相关问题