CODEWITHSUNDEEP
phpmysqlstandard-deviation
ehm
ehm

Reputation: 23767

MySQL: Standard deviation average within a table

Here's how one of my tables are structured:

id | group | val1 | val2 | val3
1  | 1     | 22   | 23   | 60
2  | 1     | 40   | 60   | 80
3  | 2     | 50   | 5    | 70
4  | 2     | ...
5  | 2     | 
6  | 3     | 
...

In my PHP-document I'm calculating the standard deviation by using val1+val2+val3 etc. per row WHERE group equals the one I'm displaying.

Now I want to know, by using MySQL, what the standard deviation is per row and the average across a group. (row1stddev+row2stddev+...)/n

I've tried using subqueries, but all I can achieve is getting a single value. I think I have a lack of understanding how the the built in standard deviation functions in MySQL actually works with multiple values.

EDIT. This is the two things I'm looking for:

id | group | stddev
1  | 1     | 21,65641
2  | 1     | 20
3  | 2     | 33,29164
4  | 2     | ...
5  | 2     | 
6  | 3     |

And average by group (average of all stddev):

group | avg_stddev 1 | 20,828205 2 | ... 3 | ...

The point of this is that I want to know in which group the difference is largest.

Upvotes: 1

Views: 2807

Answers (2)

dev-null-dweller
dev-null-dweller

Reputation: 29482

Since mysql native function STDDEV_POP accepts only column, you have to trick it by using temporary table, that will have one column with all values (that matters to standard deviation) in a row, for that use UNION ALL with 3 SELECTS, each for one meaningful column, and group it by row id. 

SELECT 
    id,
    STDDEV_POP(val) AS row_stddev 
FROM (
    SELECT id, val1 AS val FROM stdev_table
    UNION ALL
    SELECT id, val2 AS val FROM stdev_table
    UNION ALL
    SELECT id, val3 AS val FROM stdev_table
) tmp0
GROUP BY id

you can also select additional column - group and create another temporary table to select average from it:

SELECT 
    id_group,
    AVG(row_stddev) AS group_avg 
FROM (
    SELECT 
        id, 
        id_group, 
        STDDEV_POP(val) AS row_stddev 
    FROM (
        SELECT id, id_group, val1 AS val FROM stdev_table
        UNION ALL
        SELECT id, id_group, val2 AS val FROM stdev_table
        UNION ALL
        SELECT id, id_group, val3 AS val FROM stdev_table
    ) tmp0
    GROUP BY id
) tmp1
GROUP BY id_group

Upvotes: 2

pregmatch
pregmatch

Reputation: 2657

If I understood you correctly you should create View

CREATE VIEW view_name AS SELECT val1, val2, val3,  val1 + val2 + val 3 / 3 AS average FROM tablename;

and then just SELECT * FROM view_name to get you values.

Upvotes: 1

Related Questions