CODEWITHSUNDEEP
mysqlaggregate-functionsstored-functions
B. Tsai
B. Tsai

Reputation: 549

Mysql calculate the average of a function's parameter

Here is a function to describe my question:

CREATE DEFINER=`root`@`localhost` FUNCTION `Demo`(`data` Integer) RETURNS int(11)
BEGIN
    declare result integer;
    set result = avg(`data`);
    return result;
RETURN 1;
END

And the parameter data is a whole column from other select result, just looks like:

10

12

15

...

I want to use this function to calculate the average of the data, but it just shows:

Error Code: 1111. Invalid use of group function

It seems that was a wrong way to use the avg function, and other function like count() has the same problem too, but I can't find a way to achieve my purpose, is there a way to do that?

Upvotes: 0

Views: 1392

Answers (2)

Jason Myers
Jason Myers

Reputation: 38

You would have to pass it an array.

CREATE DEFINER=`root`@`localhost` FUNCTION `Demo`(a_array IN   my_integer_array) RETURNS int(11)
BEGIN

FOR  i IN  a_array.first..a_array.last  LOOP     
          set result =  a_array(i);
return result;
      END LOOP;  
RETURN 1;
END

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562771

It makes no sense to calculate the average of a single value. The average of a single value is the same as the min and the same as the max, and these are all equal to the single value itself.

It's hard to tell what you intend this function to do. If you want to use it in a query, you can calculate the average of its result as you use it on each row of a query:

SELECT AVG(Demo(data)) FROM MyTable;

Or you can use the function to query some table and return the average of many rows. But then you don't need to pass any function argument.

CREATE FUNCTION Demo() RETURNS INT
BEGIN
    DECLARE result INT;
    SET result = (SELECT AVG(`data`) FROM MyTable);
    RETURN result;
END

Upvotes: 1

Related Questions