BYates BYates - 1 year ago 97
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download