BYates BYates - 1 month ago 17
SQL Question

SQL- Need to conditionally sum a column



| PlayID | Passing_Yards | Receptions | Position | Player_Name | Rec_Yds
------------------------------------------------------------------------------
| 1234 | 0 | 1 | WR | Bill Schmill | ???
------------------------------------------------------------------------------
| 1234 | 20 | 0 | QB | Joe Schmoe | ???
------------------------------------------------------------------------------


I'm using an NFL SQL database to try and calculate how many receiving yards a player has based on a specific "play ID" number. There is no column for "receiving yards", so I need to derive that based on the "passing yards" in the same play (using the play ID). How can I create a new column for "receiving yards" that will be calculated based on the "play ID" and the "passing yards" for that play? Basically, the receptions will need to equal 1 for that play, and I assume you'd need to sum based on the play ID?

Something like:

SELECT PlayID, Passing_Yards, Receptions, Position, Player_Name, Sum(Passing_Yards)
FROM nfldb
GROUP BY PlayID, Passing_Yards, Receptions, Position, Player_Name


but that doesn't give me what I need.

Answer

I think window function will do what you want:

select n.*,
       (case when reception = 1
             then max(passing_yards) over (partition by playid)
             else 0
        end) as receiving_yards
from nfldb n;

The use of max() with over is ANSI standard syntax, which most databases support.

Comments