CODEWITHSUNDEEP
phpmysqlsql
hamza-don
hamza-don

Reputation: 465

Ignore null (empty) value mysql SELECT AVG

I have a php table which counts the AVG from mysql table. My problem is that when i have a null value(empty field) on my database table, it's counted like 0 value so the avg result will be wrong. For example, i have this on my table:

userID   question1     

 1         NULL    
 2          7

When i make a SELECT AVG(question1)from table i get: 3,5 as result(it takes 0 and 7 as values) and that's wrong because user 1 didn't answer for question1 yet, I want to ignore the null value to get AVG result = 7 (taking only 7 for value). Is there any way to do this?

Upvotes: 1

Views: 1576

Answers (3)

Jonast92
Jonast92

Reputation: 4967

Add the following to the query:

WHERE question1 IS NOT NULL

Upvotes: 1

sybear
sybear

Reputation: 7784

Have you tried:

SELECT AVG(question1)
FROM table 
WHERE 
   question1 IS NOT NULL

Upvotes: 2

Stephan
Stephan

Reputation: 426

Try to filter the SQL query to only select where the value is not null

SELECT AVG(question1) FROM ... WHERE question1 IS NOT NULL;

Upvotes: 0

Related Questions