Query for total yards allowed by defense?

I'm having trouble writing a query to show how many rushing yards a defense allowed. I can't seem to be able to figure out how to determine which team was on defense during a given play. Any ideas? Not asking anyone to write it for me, but if someone could identify the link I'm missing, it'd be much appreciated.

Response from user ccnp123

I think what you are looking is the 'pos_team' column in the 'play' table. 'pos_team' indicates the team on offense for a play, easy enough to flip around for defense.

For example, to show the respective rushing yards in the game from 10/8/2015 between Indianapolis and Houston, you can to this:

select 
  P.pos_team as team,
  sum(AP.rushing_yds) as rushing_yards
from 
  agg_play AP,
  play P,
  game G
where G.gamekey='56566' and
  G.gsis_id=AP.gsis_id and
  AP.gsis_id=P.gsis_id and
  AP.play_id=P.play_id
group by P.pos_team

and the results I get are:

------------------------
| team | rushing_yards |
------------------------
| Unk  |             0 |    
| IND  |           110 |
| HOU  |            82 |
------------------------

which you can confirm by visiting the link here: game stats

Thus, Indianapolis gave up 82 rushing yards to Houston.

I'm not sure why UNK gets in there but that should be easy enough to work around.