userdefined function for average

  • Hi guys,

    I tried to create a new userdefined function for calculating the average of top 10 values on different columns of my table:

    Example:

    SELECT AVG(a02) FROM (SELECT TOP 10 a02 FROM table01 WHERE pkart3='A' AND pkid='Y' ORDER BY lfnr DESC) u

    This works fine. But I get an error on creating this function:

    CREATE FUNCTION dbo.rollavg (@data_in char(3))

    RETURNS int

    AS

    BEGIN

    DECLARE @data_out int

    SET @data_out = AVG(u.data) FROM (SELECT TOP 10 @data_in AS data FROM table01 WHERE pkart3='A' ORDER BY lfnr DESC) u

    RETURN @data_out

    END

    I thought to use this function: SELECT dbo.rollavg(a02) AS [ra02] FROM table01

    Can anyone help me?

    Thanks a lot!

  • You're almost there!

    Try this:

    CREATE FUNCTION dbo.rollavg (@data_in char(3))

    RETURNS int

    AS

    BEGIN

    DECLARE @data_out int

    SET @data_out = (SELECT [/color]AVG(u.data) FROM (SELECT TOP 10 @data_in AS data FROM table01 WHERE pkart3='A' ORDER BY lfnr DESC) u)

    RETURN @data_out

    END

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Thanks for the answer. Unfortunately the function does not accept char-type for the AVG Operator. If I change @data_in to int (does not help because the column names are not int) the function is accepted.

    So I decided to solve this little Problem with a view:

    SELECT AVG(column1) rcol1, AVG(colmun2) rcol2, ... FROM (SELECT TOP 10 column1, column2, ... FROM table1 WHERE ...) u

    Not nice but does the Job.

    Anyhow thank for the help.

  • afuchs 47083 (4/4/2013)


    Hi guys,

    I tried to create a new userdefined function for calculating the average of top 10 values on different columns of my table:

    Example:

    SELECT AVG(a02) FROM (SELECT TOP 10 a02 FROM table01 WHERE pkart3='A' AND pkid='Y' ORDER BY lfnr DESC) u

    This works fine. But I get an error on creating this function:

    CREATE FUNCTION dbo.rollavg (@data_in char(3))

    RETURNS int

    AS

    BEGIN

    DECLARE @data_out int

    SET @data_out = AVG(u.data) FROM (SELECT TOP 10 @data_in AS data FROM table01 WHERE pkart3='A' ORDER BY lfnr DESC) u

    RETURN @data_out

    END

    I thought to use this function: SELECT dbo.rollavg(a02) AS [ra02] FROM table01

    Can anyone help me?

    Thanks a lot!

    Yes - don't do it!!

    For the sake of a little code simplification, the cost of using a scalar udf simply isn't worth it. Scalar udf's prohibit parallelism in all or part of the execution plan and are executed for every row of the part of the plan where they are called.

    On the other hand, if you're seeking an average for a range of different values for pkart3 then there are tricks you could use, such as an iTVF or just a plain ol' CROSS APPLY.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply