Use a For Loop to alternate a set of ID's in a SQL query

Question

I have a query calculating some gambling statistics for just one 'team_id' but I would like to calculate for all team_id's to create reports/graphs. I do know some python and wondering if a "for loop" would be most efficient for this. I will be creating many queries similar to this one but need to add the team_id in the Select portion(next to ATS_Status) of the query and include every team. Any suggestions would be great.

 select ATS_Status, round((cast(count(*) as float) /75*100),2) as ATS_Percentage 
      from (select 
     (case  when cast(g2.pts as float)-cast(g1.pts as float) < cast(spread2 as float) then 'Covered'
            when cast(g2.pts as float)-cast(g1.pts as float) = cast(spread2 as float) then 'Push'
            else 'Loss' end) as ATS_Status
     from nba_games_all g1
     left outer join nba_games_all g2 on g1.game_id = g2.game_id
     and g1.team_id =g2.a_team_id
     left join nba_teams_all t on t.team_id=g1.a_team_id
     left join nba_betting_spread bs on g1.game_id = bs.game_id
     and book_name = 'Bovada'
     left join nba_betting_money_line ml on g1.game_id = ml.game_id
     and ml.book_name = 'Bovada'
     where g1.team_id = '1610612759' 
     and g1.season_year = '2017' 
     and g1.season_type = 'Regular Season'
     and g1.game_date >= Convert(datetime, '2017-11-02'))a
     group by ATS_Status

question from:https://stackoverflow.com/questions/65931661/use-a-for-loop-to-alternate-a-set-of-ids-in-a-sql-query

Answer 1

You can use team_id in the group by and remove the particular condition g1.team_id = '1610612759' as follows:

select ATS_Status, team_id, round((cast(count(*) as float) /75*100),2) as ATS_Percentage 
      from (select 
     (case  when cast(g2.pts as float)-cast(g1.pts as float) < cast(spread2 as float) then 'Covered'
            when cast(g2.pts as float)-cast(g1.pts as float) = cast(spread2 as float) then 'Push'
            else 'Loss' end) as ATS_Status, g1.team_id
     from nba_games_all g1
     left outer join nba_games_all g2 on g1.game_id = g2.game_id
     and g1.team_id =g2.a_team_id
     left join nba_teams_all t on t.team_id=g1.a_team_id
     left join nba_betting_spread bs on g1.game_id = bs.game_id
     and book_name = 'Bovada'
     left join nba_betting_money_line ml on g1.game_id = ml.game_id
     and ml.book_name = 'Bovada'
     where --g1.team_id = '1610612759' 
     g1.season_year = '2017' 
     and g1.season_type = 'Regular Season'
     and g1.game_date >= Convert(datetime, '2017-11-02'))a
     group by ATS_Status, team_id