Question

I have a relation pitching (playerid, yearid, stint, ..., sv, ...)

players can play multiple stints in a given year, but (playerid, yearid, stint) should be unique.

I need to find the number of players, COUNT(playerid), who had over 40 saves, sv>40, in any single year since 1975 using an sql query... Ex.

Player01, 1985, 1, ... , 30, ...

Player01, 1985, 2, ... , 20, ...

would increase COUNT because player01 got 50 saves in 1985, even

though it was in two separate stints

SQL, PostgreSQL

Answer 1

select playerid,year,count(1) from (select playerid, year, sum(sv) saves from pitching group by playerid, year having year>=1975) t where t.saves>40
group by playerid,year