CODEWITHSUNDEEP
sqlsql-server
Sun Louie
Sun Louie

Reputation: 1

SQL Create Function

Wants to create a function of the average product standardcost for the same color, and using 'red' to test . But NULL returned. If only run the SELECT,FROM,WHERE,GROUP BY, it will work file and return a value.

Here's my code : 

CREATE FUNCTION avgv (@Color CHAR)
RETURNS CHAR 
AS 
BEGIN
        DECLARE @AVG CHAR ;
        SET @AVG = (SELECT P.Color  AVG(P.StandardCost)  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color
        GROUP BY Color)

        Return @AVG

END
GO
SELECT dbo.avgv('red')

Please let me know how to fix, thanks!

Upvotes: 0

Views: 150

Answers (1)

M. Rezaeyan
M. Rezaeyan

Reputation: 408

Input parameter: char -> varchar(100)
Returns -> char -> int for integer result /decimal(18, 2) for float result
Remove Group By

Function 

CREATE FUNCTION avgv (@Color varchar(100))
RETURNS real
AS 
BEGIN
        DECLARE @AVG real;
        SET @AVG = (SELECT AVG(ISNULL(P.StandardCost, 0))  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color)

        Return @AVG

END
GO
SELECT dbo.avgv('red')

Procedure 

CREATE PROCEDURE avgv (@Color varchar(100))
AS 
BEGIN
        SELECT AVG(ISNULL(P.StandardCost, 0))  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color
END

Upvotes: 1

Related Questions