set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[GetPlayerAvg]
( @playerid int,@calcType varchar(20) )
RETURNS float
AS
BEGIN
declare @overAll as float,
declare @physic as float,
declare @technic as float


if @calcType='overall'
begin
set @physic=SELECT ([Work]+ Weight+ Height+ RightFoot+ LeftFoot+ Strength+ Stamina+ Pace+ Fitness+ Jumping+ Injury+ Agility+ Acceleration)/13
FROM         PhisicalPropertiesTable
where playerid=@playerid

set @technic=SELECT  (Goalkeeper+ Corners+Crossing+Dribbling+Finishing+FirstTouch+Heading+LongShots+LongThrows+Marking+Passing+Penalty+Tackling+ThrowIns+
                      Aerial+AreaCommand+Communication+Handling+Kicking+OneOnOne+Reflex+Throwing+Positioning+OffBall+Creativity+Leadership+ShootingPower+
                      LongPass+Crosses)/29
FROM         TechnicalPropertiesTable
where playerid=@playerid

@overAll=(@technic+@physic)/2

return (@overAll)

end

else if @calcType='physic'
begin

set @physic=SELECT ([Work]+ Weight+ Height+ RightFoot+ LeftFoot+ Strength+ Stamina+ Pace+ Fitness+ Jumping+ Injury+ Agility+ Acceleration)/13
FROM         PhisicalPropertiesTable
where playerid=@playerid
return(@physic)


end

else if @calcType='technic'
begin
set @technic=SELECT     (Goalkeeper+ Corners+Crossing+Dribbling+Finishing+FirstTouch+Heading+LongShots+LongThrows+Marking+Passing+Penalty+Tackling+ThrowIns+
                      Aerial+AreaCommand+Communication+Handling+Kicking+OneOnOne+Reflex+Throwing+Positioning+OffBall+Creativity+Leadership+ShootingPower+
                      LongPass+Crosses)/29
FROM         TechnicalPropertiesTable
where playerid=@playerid
return (@technic)

end


END

quote:

---

Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 7
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 8
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 13
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 17
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure GetPlayerAvg, Line 23
Incorrect syntax near '@overAll'.
Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 32
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure GetPlayerAvg, Line 42
Incorrect syntax near the keyword 'SELECT'.

---

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[GetPlayerAvg]
( @playerid int,@calcType varchar(20) )
RETURNS float
AS
BEGIN
declare @physic as float,
@technic as float,
@result as float


if @calcType='overall'
begin
SELECT @physic= ([Work]+ Weight+ Height+ RightFoot+ LeftFoot+ Strength+ Stamina+ Pace+ Fitness+ Jumping+ Injury+ Agility+ Acceleration)/13
FROM         PhisicalPropertiesTable
where playerid=@playerid

SELECT @technic= (Goalkeeper+ Corners+Crossing+Dribbling+Finishing+FirstTouch+Heading+LongShots+LongThrows+Marking+Passing+Penalty+Tackling+ThrowIns+
                      Aerial+AreaCommand+Communication+Handling+Kicking+OneOnOne+Reflex+Throwing+Positioning+OffBall+Creativity+Leadership+ShootingPower+
                      LongPass+Crosses)/29
FROM         TechnicalPropertiesTable
where playerid=@playerid

set @result=(@technic+@physic)/2


end

else if @calcType='physic'
begin

SELECT  @physic= ([Work]+ Weight+ Height+ RightFoot+ LeftFoot+ Strength+ Stamina+ Pace+ Fitness+ Jumping+ Injury+ Agility+ Acceleration)/13
FROM         PhisicalPropertiesTable
where playerid=@playerid
set @result=@physic


end

else if @calcType='technic'
begin
 SELECT  @technic=   (Goalkeeper+ Corners+Crossing+Dribbling+Finishing+FirstTouch+Heading+LongShots+LongThrows+Marking+Passing+Penalty+Tackling+ThrowIns+
                      Aerial+AreaCommand+Communication+Handling+Kicking+OneOnOne+Reflex+Throwing+Positioning+OffBall+Creativity+Leadership+ShootingPower+
                      LongPass+Crosses)/29
FROM         TechnicalPropertiesTable
where playerid=@playerid
set @result=@technic

end
return (@result)

END

ALTER FUNCTION dbo.GetPlayerAvg
(
	@PlayerID INT,
	@CalcType VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @Physic AS FLOAT,
		@Technic AS FLOAT

	IF @Calctype IN ('Overall', 'Physic', 'Technic')
		SELECT	@Physic = [Work] + Weight + Height + RightFoot + LeftFoot + Strength + Stamina + Pace + Fitness + Jumping + Injury + Agility + Acceleration,
			@Technic = Goalkeeper + Corners + Crossing + Dribbling + Finishing + FirstTouch + Heading + LongShots + LongThrows + Marking + Passing + Penalty + Tackling + ThrowIns + Aerial + AreaCommand + Communication + Handling + Kicking + OneOnOne + Reflex + Throwing + Positioning + OffBall + Creativity + Leadership + ShootingPower + LongPass + Crosses
		FROM	PhisicalPropertiesTable
		WHERE	PlayerID = @PlayerID

	RETURN	CASE
			WHEN @CalcType = 'Overall' THEN (@Physic + @Technic) / (13.0 + 29.0)
			WHEN @CalcType = 'Physic' THEN @Physic / 13.0
			WHEN @CalcType = 'Technic' THEN @Technic / 29.0
		END
END

quote:

---

Originally posted by lior3790


BTW,

if i need to get the avg of more than one row should i get rid of the
params @playerId?

or there's a different way to do so.

---

ALTER FUNCTION dbo.GetPlayerAvg
(
	@PlayerID INT,
	@CalcType VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @Physic AS FLOAT,
		@Technic AS FLOAT,
		@Records AS FLOAT

	IF @Calctype IN ('Overall', 'Physic', 'Technic')
		SELECT	@Physic = SUM([Work] + Weight + Height + RightFoot + LeftFoot + Strength + Stamina + Pace + Fitness + Jumping + Injury + Agility + Acceleration),
			@Technic = SUM(Goalkeeper + Corners + Crossing + Dribbling + Finishing + FirstTouch + Heading + LongShots + LongThrows + Marking + Passing + Penalty + Tackling + ThrowIns + Aerial + AreaCommand + Communication + Handling + Kicking + OneOnOne + Reflex + Throwing + Positioning + OffBall + Creativity + Leadership + ShootingPower + LongPass + Crosses),
			@Records = COUNT(*)
		FROM	PhisicalPropertiesTable
		WHERE	PlayerID = COALESCE(@PlayerID, PlayerID)

	RETURN	CASE
			WHEN @CalcType = 'Overall' THEN (@Physic + @Technic) / (13.0 + 29.0)
			WHEN @CalcType = 'Physic' THEN @Physic / 13.0
			WHEN @CalcType = 'Technic' THEN @Technic / 29.0
		END / @Records
END

ALTER FUNCTION dbo.GetPlayerAvg
(
	@PlayerID INT,
	@CalcType VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @Physic AS FLOAT,
		@Technic AS FLOAT,
		@Records AS FLOAT,
		@Result as Float
	
	Set @Result = 0

	IF @Calctype IN ('Overall', 'Physic', 'Technic')
		Select
			@Result = 
				CASE
					WHEN @CalcType = 'Overall' THEN (Physic + Technic) / (13.0 + 29.0)
					WHEN @CalcType = 'Physic' THEN Physic / 13.0
					WHEN @CalcType = 'Technic' THEN Technic / 29.0
				END / Records
		From
			(
			SELECT	Physic = SUM([Work] + Weight + Height + RightFoot + LeftFoot + Strength + Stamina + Pace + Fitness + Jumping + Injury + Agility + Acceleration),
				Technic = SUM(Goalkeeper + Corners + Crossing + Dribbling + Finishing + FirstTouch + Heading + LongShots + LongThrows + Marking + Passing + Penalty + Tackling + ThrowIns + Aerial + AreaCommand + Communication + Handling + Kicking + OneOnOne + Reflex + Throwing + Positioning + OffBall + Creativity + Leadership + ShootingPower + LongPass + Crosses),
				Records = COUNT(*)
			FROM	PhisicalPropertiesTable
			WHERE	PlayerID = COALESCE(@PlayerID, PlayerID)
			) t

	RETURN(@Result)
END

ALTER FUNCTION dbo.GetPlayerAvg
(
	@PlayerID INT,
	@CalcType VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
	RETURN	(
			SELECT	CASE
					WHEN @CalcType = 'Overall' THEN (x.Physic + x.Technic) / (13.0 + 29.0)
					WHEN @CalcType = 'Physic' THEN x.Physic / 13.0
					WHEN @CalcType = 'Technic' THEN x.Technic / 29.0
				END / NULLIF(x.Records, 0)
			FROM	(
					SELECT	SUM([Work] + Weight + Height + RightFoot + LeftFoot + Strength + Stamina + Pace + Fitness + Jumping + Injury + Agility + Acceleration) AS Physic,
						SUM(Goalkeeper + Corners + Crossing + Dribbling + Finishing + FirstTouch + Heading + LongShots + LongThrows + Marking + Passing + Penalty + Tackling + ThrowIns + Aerial + AreaCommand + Communication + Handling + Kicking + OneOnOne + Reflex + Throwing + Positioning + OffBall + Creativity + Leadership + ShootingPower + LongPass + Crosses) AS Technic,
						COUNT(*) AS Records
					FROM	PhisicalPropertiesTable
					WHERE	@PlayerID IS NULL
						OR @PlayerID = PlayerID
				) AS x
		)
END

playerid      AVG
1             5.5
2             8.4
3             12.1
4             6.3
5             2.7