How to Select Average from many Column with extrawhere from all Column? 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 &gt; 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 &gt; 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.
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]&gt; select * from abc;
+------+------+------+
| id   | ph   | ph2  |
+------+------+------+
|    1 | 10   | 20   |
|    2 | NA   | 20   |
|    3 | 11   | 10   |
+------+------+------+
3 rows in set (0.00 sec)

MariaDB [test]&gt; 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)