In MySQL is there some way to calculate average with a list of numbers?

Question

I would like to do something like the following:

SELECT AVG([1,2,3]);

This would of course return 2. The closest I've got is the following which is less than ideal but can be put in one line:

drop table nums;
create temporary table nums (num int);
insert into nums(num) values(1),(2),(3);
select avg(num) from nums;

If there's a way I would assume this would also be possible with other functions such as variance() and others.

Edit: This idea is out of curiosity not a real problem I need to solve.

MySQL is not a general purpose programming language, so it prefers things expressed as tables like you have there. — tadman, Jul 26 '18 at 20:14
@PaulSpiegel nice... but other functions such as variance() would be hard to produce... — loco.loop, Jul 26 '18 at 20:17
Perhaps providing more information of what you want to achieve would make it so people provide you a better way of solving your problem? I would suspect you are using the wrong tools. — Andrius Naruševičius, Jul 26 '18 at 20:17
@AndriusNaruševičius I actually wanted to know the variance() of a list using MySQL but I couldn't figure out how and just got curious... I put avg on the question because it's an easier function. — loco.loop, Jul 26 '18 at 20:18

score 1 · Answer 1 · answered Jul 26 '18 at 20:19

1

AVG can only have 1 argument. You'd need to do SELECT AVG(num) FROM nums

You could also do SELECT SUM(num) / COUNT(num) FROM nums

Just know since you're dividing using ints that it will not be precise.

answered Jul 26 '18 at 20:19

Paul Shumaker

98
7

score 0 · Answer 2 · answered Jul 26 '18 at 20:23

You are using the wrong tools to solve your problem.

If you want to calculate the variance of a list, use some kind of a scripting language, be it Php, Python, etc. If you want to firstly store the data and only then calculate the variance, of course, use something like MySql.

juzraai · Answer 3 · 2018-07-27T12:25:45.440

I also think that MySQL may not be the right tool (since you didn't want to store the numbers), but the answer to your question is: yes, you can do it with MySQL without creating tables if you want.

I didn't find any built-in functions/structures for this, but I came up with the following solution. My idea is to create a custom function which accepts the numbers in a delimited string, then it splits the string and calculates the average of the numbers. Here's an implementation which works with integers. The input should be num,num,num and so on, it should end with a number (see the examples at the end).

DROP FUNCTION IF EXISTS AVGS;
DELIMITER $$
CREATE FUNCTION AVGS(s LONGTEXT) RETURNS DOUBLE
DETERMINISTIC
BEGIN
    DECLARE sum BIGINT DEFAULT 0;
    DECLARE count BIGINT DEFAULT 0;
    DECLARE pos BIGINT DEFAULT 0;
    DECLARE lft TEXT DEFAULT '';

    -- can we split?
    SET pos = LOCATE(',', s);
    WHILE 0 < pos DO -- while we can split
        SET lft = LEFT(s, pos - 1); -- get the first number
        SET s = SUBSTR(s FROM pos + 1); -- store the rest
        SET sum = sum + CAST(lft AS SIGNED);
        SET count = count + 1;
        SET pos = LOCATE(',', s); -- split again
    END WHILE;

    -- handle last number
    SET sum = sum + CAST(s AS SIGNED);
    SET count = count + 1;

    RETURN sum / count;
END $$
DELIMITER ;

SELECT AVGS("1");     -- prints: 1    
SELECT AVGS("1,2");   -- prints: 1.5
SELECT AVGS("1,2,3"); -- prints: 2

See the live working demo here.

Variance may be much more complex, but I hope you get the idea.

Didn't read the code, I assume it works. Seems like this would be the way to go... — loco.loop, Jul 26 '18 at 20:55

In MySQL is there some way to calculate average with a list of numbers?

3 Answers3