How to Select Average from many Column with extrawhere from all Column?

Question

This Query is Simple, but I still don't know to handle it.

I must Select Average From Many Column But Only Column with Integer Value.

like this

SELECT AVG(SET_O_1A) AS SET_O_1A,
       AVG(REAL_O_1A) AS REAL_O_1A,
       AVG(SET_O_1B) AS SET_O_1B,

       AVG(REAL_O_1B) AS REAL_O_1B,
       AVG(SET_PRE_1A) AS SET_PRE_1A,

       AVG(REAL_PRE_1A) AS REAL_PRE_1A,
       AVG(SET_PRE_1B) AS SET_PRE_1B,

       AVG(REAL_PRE_1B) AS REAL_PRE_1B,
       AVG(SET_O_2A) AS SET_O_2A,

       AVG(REAL_O_2A) AS REAL_O_2A,
       AVG(SET_O_2B) AS SET_O_2B,

       AVG(REAL_O_2B) AS REAL_O_2B

        FROM table
        WHERE ALL column > 0;

How to Fix Problem like this?

I have try like this but still not working

SELECT * FROM (SELECT AVG(SET_O_1A) AS SET_O_1A,
           AVG(REAL_O_1A) AS REAL_O_1A,
           AVG(SET_O_1B) AS SET_O_1B,

           AVG(REAL_O_1B) AS REAL_O_1B,
           AVG(SET_PRE_1A) AS SET_PRE_1A,

           AVG(REAL_PRE_1A) AS REAL_PRE_1A,
           AVG(SET_PRE_1B) AS SET_PRE_1B,

           AVG(REAL_PRE_1B) AS REAL_PRE_1B,
           AVG(SET_O_2A) AS SET_O_2A,

           AVG(REAL_O_2A) AS REAL_O_2A,
           AVG(SET_O_2B) AS SET_O_2B,

           AVG(REAL_O_2B) AS REAL_O_2B

            FROM table) as ORI
            WHERE ORI > 0;

Can Anyone help this? Thanks in Advance

Tabel like this

ID   |  ph  |  ph2
========================
 1   | 10    |  20
 2   | NA    |  20
 3   | 11    |  10

and I Hope the result like this

ID   |  ph  |  ph2
========================
 1   | 10.5 |  16.67

so The query only average column with number only.

Answer 1

I used REGEXP to eliminate the text from the column. This may not be the best answer but may work for learning purpose.

MariaDB [test]> select * from abc;
+------+------+------+
| id   | ph   | ph2  |
+------+------+------+
|    1 | 10   | 20   |
|    2 | NA   | 20   |
|    3 | 11   | 10   |
+------+------+------+
3 rows in set (0.00 sec)

MariaDB [test]> select (select avg(ph) from abc where ph regexp '^[0-9]+$') as avg_ph, (select avg(ph2) from abc where ph2 regexp '^[0-9]+$') as avg_ph2 from dual;
+--------+--------------------+
| avg_ph | avg_ph2            |
+--------+--------------------+
|   10.5 | 16.666666666666668 |
+--------+--------------------+
1 row in set (0.00 sec)

Answer 2

I have Found, with NULLIF mysql function

like this

SELECT AVG(NULLIF(SET_O_1A,0)) AS SET_O_1A,
       AVG(NULLIF(REAL_O_1A,0)) AS REAL_O_1A,
       AVG(NULLIF(SET_O_1B,0)) AS SET_O_1B,

       AVG(NULLIF(REAL_O_1B,0)) AS REAL_O_1B,
       AVG(NULLIF(SET_PRE_1A,0)) AS SET_PRE_1A,

       AVG(NULLIF(REAL_PRE_1A,0)) AS REAL_PRE_1A,
       AVG(NULLIF(SET_PRE_1B,0)) AS SET_PRE_1B,

       AVG(NULLIF(REAL_PRE_1B,0)) AS REAL_PRE_1B,
       AVG(NULLIF(SET_O_2A,0)) AS SET_O_2A,

       AVG(NULLIF(REAL_O_2A,0)) AS REAL_O_2A,
       AVG(NULLIF(SET_O_2B,0)) AS SET_O_2B,

       AVG(NULLIF(REAL_O_2B,0)) AS REAL_O_2B
        FROM table;

Answer 3

Try this :

 select avg(if(t.ph regexp '^[0-9]+$',t.ph,NULL)) ph,avg(if(t.ph2 regexp '^[0-9]+$',t.ph2,NULL)) ph2
from abc t;

or

select cast(max(t1.ph)/max(t1.ph_cnt) as decimal(15,2)) avg_ph,cast(max(t1.ph2)/max(t1.ph2_cnt) as decimal(15,2)) avg_ph2
from
 (select if(t.ph regexp '^[0-9]+$',@ph1_cnt:=@ph1_cnt+1,0) ph_cnt,if(t.ph regexp '^[0-9]+$',@ph1_tot:=@ph1_tot+t.ph,0) ph,
    if(t.ph2 regexp '^[0-9]+$',(@ph2_cnt:=@ph2_cnt+1),0) ph2_cnt,if(t.ph2 regexp '^[0-9]+$',@ph2_tot:=@ph2_tot+t.ph2,0) ph2
 from test.abc t,(select @ph1_cnt:=0,@ph1_tot:=0,@ph2_cnt:=0,@ph2_tot:=0) x
 ) t1;