# 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 > 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.

• Provide table structure with sample data and output. Commented Aug 12, 2016 at 3:51
• @JSapkota I have Edited Sir Commented Aug 12, 2016 at 4:07
• What does "ALL" mean in `WHERE ALL column > 0`? Commented Aug 14, 2016 at 0:36
• `column > 0` does not test for "Column with Integer Value". Please clarify. Commented Aug 14, 2016 at 0:37

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)
``````
• Thanks for the answer, in fact I have other answer use this AVG(NULLIF(PH2 ,0))AS PH2 , use NULLIF, but it still need edit a lot of code. Any other Idea? Commented Aug 12, 2016 at 6:04

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;
``````

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;
``````