# How to get Average value from one row if have zero or null values in mysql

I'm new in mysql. I want to ask something about get average value from one row in mysql when have a null or zeros value on them.

the image below assuming my table

And I want to get the average from each column p_01,l_01,p_02,l_01,p_03,l_03. How i can do that in mysql query..??

or how to use (count query) in one row to get the value from column p_01,l_01,p_02,l_02.... p_n,l_n. I will use that count value for division operation.

Thank You in advance. Best Regards.

• What is your definition for the average of null, 1? – Lennart Dec 10 '18 at 14:49
• hi mr Lennart, glad to meet you again. no, assuming the null is empty value by default mysql. – epm.007 Dec 10 '18 at 14:55
• What would the result be? 1, 1/2, null or something else? – Lennart Dec 10 '18 at 14:57
• @Lennart, result is null. assuming the 01,02,03,04...n is a date. when user entry column in 01, 03 and 04. then the result i wanted like this formula (01+03+04)/3. how i can get 3 value..? or you have any suggestion..?? thank you. – epm.007 Dec 10 '18 at 15:06
• I'm having trouble understanding what you are trying to do. Can you create a db<>fiddle or similar, together with the expected results. – Lennart Dec 10 '18 at 15:23

``````SELECT AVG(p_01), AVG(l_01), AVG(p_02), AVG(l_01), AVG(p_03), AVG(l_03)
will ignore `NULLs`, but include zeros in the computations. If that is not what you want, then more clearly explain your requirements, and provide example that includes both `0` and `NULL` together with the desired result. (And you don't need to use a full 6 columns to formulate the question.)