Need SQLQuery with Stored procedure with below example-CodePudding

I have the following MySQL-table

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

the average must be using stored procedure, not normal query.

I have normal 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：

If the Id's on your table are unique, you can do it like this (you were pretty close... just the case when was missed in your query):

select 
  Id, 
  (ifnull(one,0)   ifnull(two,0)   ifnull(three,0))/ 
  (
    (case when one is not null then 1 else 0 end)   
    (case when two is not null then 1 else 0 end)   
    (case when three is not null then 1 else 0 end) 
  ) as average
from {your_table}

You can test on this db<>fiddle