Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

userdefined function for average Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 2, Visits: 11
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!
Post #1438673
Posted Thursday, April 4, 2013 2:25 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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

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
Post #1438680
Posted Monday, April 15, 2013 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:14 AM
Points: 2, Visits: 11
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.
Post #1442218
Posted Monday, April 15, 2013 3:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:40 AM
Points: 6,778, Visits: 13,971
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1442229
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse