Hi, I have seen this discussion going on about NULL values and wanted to share my 2 cents:
MySQL does not include NULL values in aggregate functions such as - COUNT(expression) - COUNT(DISTINCT expression) - AVG(expression) - SUM(expression) - MIN(expression) - MAX(expression) - STDDEV(expression) - ... Expressions that return NULL values are treated as not being existant for these aggregate functions. COUNT(column) will fall into the above so NULL values would be excluded when counting. COUNT(*) is different as it does include NULL values, so it is more like a row/record count, not a count on a specific column or expression (even on a one-column table). I think all other databases I used in the past had about the same behaviour. Best regards J -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of K. John Wu Sent: Tuesday, January 12, 2010 8:54 PM To: FastBit Users Subject: Re: [FastBit-users] NULL values Hi, Andreas, Thanks for the feedback. What I interpret MySQL is doing is that it is silently dropping the null values in the results. Internally, I presume MySQL is actually counting the row with null value in the result set, it only exclude the null value when computing the average. FastBit does something slightly different, it exclude the null values from the result set. In your MySQL example, if COUNT(*) returns 1, it would indicate MySQL is doing the same thing as FastBit. Guess we have some work to do to make FastBit conform to the SQL standard in this regard. John On 1/12/2010 11:05 AM, Andreas Streichardt wrote: > >> SELECT AVG(a),COUNT(a) FROM test; >> +--------+----------+ >> >> | AVG(a) | COUNT(a) | >> >> +--------+----------+ >> >> | 2.0000 | 1 | >> >> +--------+----------+ >> >> This is more or less the same you just did in fastbit. > > And this is what i really meant ;): > > mysql> SELECT AVG(a),COUNT(*) FROM test; > +--------+----------+ > | AVG(a) | COUNT(*) | > +--------+----------+ > | 2.0000 | 2 | > +--------+----------+ > > >> >> SELECT AVG(a),COUNT(a) FROM test; >> +--------+----------+ >> >> | AVG(a) | COUNT(a) | >> >> +--------+----------+ >> >> | 2.0000 | 1 | >> >> +--------+----------+ >> >> This is of course a bit different as COUNT on a specific column doesn't >> count the NULLs of this column. >> > > _______________________________________________ > FastBit-users mailing list > [email protected] > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
