Home Stored Functions UDF Function to calculate average of unlimited values

Function to calculate average of unlimited values

Function Name:
get_average_unlimited
Function type:
Select
Input variables:
String
Output:
Returns single integer
Uses function:
CAST(), LOCATE(), SUBSTRING()

The purpose of this function is to parse a string of integer values using a WHILE LOOP , and calculate the average of the values. The function uses the CAST(), LOCATE() and SUBSTRING() functions to parse the input string data

Function

DELIMITER $$
DROP FUNCTION IF EXISTS `sportszone`.`get_average_unlimited` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_average_unlimited`(stat_str text) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE stat INT;
DECLARE stat_avg INT;
DECLARE stat_cnt INT;
DECLARE stat_sum INT;
SET stat_avg = 0;
SET stat_cnt = 0;
SET stat_sum = 0;
WHILE stat_str <> '' DO
select CAST(SUBSTRING(stat_str FROM 1 FOR LOCATE(',', stat_str, 1) - 1) AS UNSIGNED) INTO stat;
SET stat_cnt = stat_cnt + 1;
SET stat_sum = stat_sum + stat;
Select SUBSTRING(stat_str,LOCATE(',', stat_str, 1)+1) INTO stat_str;
END WHILE;
SET stat_avg = stat_sum/stat_cnt;
RETURN stat_avg;
END $$
DELIMITER ;

Select Function Examples

Select get_average_unlimited('289,309,275,315,') as 'Batting Average over 4 Seasons';

Result Set

Batting Average over 4 Seasons

297

Select get_average_unlimited('289,309,275,315,401,275,') as 'Batting Average over 6 Seasons';

Result Set

Batting Average over 6 Seasons

311

Who's Online
We have 33 guests online