ALTER PROCEDURE usp_GetSeasonStatsByTeamID
	@teamid int,
	@season int
AS

declare @playertable TABLE(
	playerid int,
	playername nvarchar(50))

insert into @playertable(playerid, playername)
select id, playerlastname from dbo.player where teamid=@teamid AND active=1


SELECT 
     p.playerid,
	 p.playername,
	 SUM(o.innings) as 'I',
     SUM(o.atbats) as 'AB', 
     SUM(o.runs) as 'R', 
     SUM(o.hits) as 'H',
     SUM(o.singles) as '1B',
     SUM(o.doubles) as '2B', 
     SUM(o.triples) as '3B',
     SUM(o.homeruns) as 'HR', 
     SUM(o.runsbattedin) as 'RBI', 
     SUM(o.baseonballs) as 'BB', 
     SUM(o.strikeouts) as 'K', 
     SUM(o.sacrificeflys)  as 'SF', 
     SUM(o.sacrificehits) as 'SH', 
     SUM(o.stolenbases) as 'SB', 
     SUM(o.errors) as 'E',
	cast(
		cast(
			SUM(o.hits) + 
			SUM(o.baseonballs) as decimal
			)
			/
			case 
				when 
					SUM(o.atbats) + 
					SUM(o.baseonballs) + 
					SUM(o.sacrificeflys) + 
					SUM(o.sacrificehits) = 0 then 1 
				else
					cast(
						SUM(o.atbats) + 
						SUM(o.baseonballs) + 
						SUM(o.sacrificeflys) + 
						SUM(o.sacrificehits) as decimal
						)
				end	
			as decimal(4,3)
			) as 'OBP',

     cast(
		cast(
			SUM(o.singles) + 
			SUM((o.doubles*2)) + 
			SUM((o.triples*3)) + 
			SUM((o.homeruns*4))as decimal
			)
			/
			case 
				when 
					SUM(o.atbats) = 0 then 1 
				else
					cast(
						SUM(o.atbats) as decimal
						) 
			end	
			as decimal(4,3)
			) as 'SLG', 

	cast(
		cast(
			SUM(o.hits) as decimal
			)
			/
		case 
			when 
				SUM(o.atbats) = 0 then 1 
			else
				cast(
					SUM(o.atbats) as decimal
					)
		end	
		as decimal(4,3)
		) as 'AVG'

from @playertable as p 
left join offensivestats as o on p.playerid=o.playerid
Where o.playerid=p.playerid AND o.season=@season
group by p.playerid, p.playername