AVG help

squallm1

Hi! I need help with a MySQL query:

I want to make an average from some scores, but some records are NULLS, the problem is that 0 is a valid number for the scores and the default for NULL is 0, so the avg counts all records (including the NULLS like 0s) so is not accurate.

Do I have to change the 0 values to something like 0.1 or there´s another way to do it?

Thanks a lot!

Miguel Mendez.

Sxooter

Hmmm. Maybe this is a MySQL bug?

In postgresql, I get this:

smarlowe=# create table test (id int, score float);
CREATE TABLE
smarlowe=# insert into test values (0,98.5);
INSERT 5020790 1
smarlowe=# insert into test values (0,79.3);
INSERT 5020791 1
smarlowe=# select avg(score) from test group by id;

avg

88.9
(1 row)

smarlowe=# insert into test values (0,NULL);
INSERT 5020792 1
smarlowe=# select avg(score) from test group by id;

avg

88.9
(1 row)

MySQL seems to make some other bad decisions regarding nulls as well.

I'd recommend switching to a real database, but that's just my opinion.

Sxooter

Maybe this will work:

select id,avg(score) from test where score is not null group by id

And you won't have to change databases.

squallm1

Thanks Sxooter!!

I was confused because on phpMyAdmin the NULL values appears like 0.

So I use IS NOT NULL and worked OK!

Thanks again!

Miguel Mendez.