I have a table in MySQL with three columns that need an average of each row of three columns using the stored procedure:

Id  |  One  |  Two  |  Three
---- ------- ------- -------
1   |  10   |  30   |  20
2   |  50   |  60   |  20
3   |  60   |  0    |  40

The average must be determined using a stored procedure, not a normal query.

I have this SQL query

select
    id, 
    (ifnull(one, 0)   ifnull(two, 0)   ifnull(three, 0)) /
        ((one is not null)   (two is not null)   (three is not null)) as average 
from table

I want that to look like this, with a MySQL query:

Id | Average
--- --------
1  | 20
2  | 43.3
3  | 50

CodePudding user response：

Maybe not the best solution but you could use:

select id, 
       SUM(coalesce(one,0) coalesce(two,0) coalesce(three,0)) /
       count(CASE WHEN one != 0 and one is not null  then 1 ELSE NULL END) 
         count(CASE WHEN two != 0 and two is not null  then 1 ELSE NULL END) 
         count(CASE WHEN three  != 0 and three is not null  then 1 ELSE NULL END )  as average 

from my_table
group by id;

Result:

id    average
1     20.0000
2     43.3333
3     50.0000
4     35.0000

Demo

This query excludes Null and 0 values

coalesce

Full Procedure

DELIMITER//
CREATE PROCEDURE average()
BEGIN 

    select id, SUM(coalesce(one,0) coalesce(two,0) coalesce(three,0)) /(count(CASE WHEN one != 0 and one is not null  then 1 ELSE NULL END)   count(CASE WHEN two != 0 and two is not null  then 1 ELSE NULL END)   count(CASE WHEN three  != 0 and three is not null  then 1 ELSE NULL END))  as average from my_table group by id ;

END
DELIMITER ;