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

enter image description here

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)
    FROM tbl

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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