0

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.

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

3 Answers 3

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)
1
  • 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?
    – Uchsun
    Aug 12, 2016 at 6:04
0

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.