Need SQLQuery with Stored procedure with below example

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

Answer

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