Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 userdefined function for average Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, April 04, 2013 1:56 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, April 15, 2013 4:13 AM Points: 2, Visits: 10
 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) uThis works fine. But I get an error on creating this function:CREATE FUNCTION dbo.rollavg (@data_in char(3))RETURNS intASBEGIN 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_outENDI thought to use this function: SELECT dbo.rollavg(a02) AS [ra02] FROM table01Can anyone help me?Thanks a lot!
Post #1438673
 Posted Thursday, April 04, 2013 2:25 AM
 SSChasing Mays Group: General Forum Members Last Login: 2 days ago @ 7:08 AM Points: 630, Visits: 2,959
 You're almost there!Try this:CREATE FUNCTION dbo.rollavg (@data_in char(3))RETURNS intASBEGINDECLARE @data_out intSET @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_outEND ---------------------------------------------------------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 lensSociety 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 Group: General Forum Members Last Login: Monday, April 15, 2013 4:13 AM Points: 2, Visits: 10
 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 ...) uNot nice but does the Job.Anyhow thank for the help.
Post #1442218
 Posted Monday, April 15, 2013 3:53 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:47 AM Points: 6,317, Visits: 12,145
 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) uThis works fine. But I get an error on creating this function:CREATE FUNCTION dbo.rollavg (@data_in char(3))RETURNS intASBEGIN 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_outENDI thought to use this function: SELECT dbo.rollavg(a02) AS [ra02] FROM table01Can 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1442229

 Permissions