CODEWITHSUNDEEP
mysqlcountnull
Chrome123
Chrome123

Reputation: 15

Getting incorrect result when using IS NOT NULL with COUNT function - MySQL

I have a table of music tracks with a length field that stores information on how long each individual track is. I'm trying to query to database to find out the maximum track length, minimum track length and I want the return the number of tracks which have a length listed (IS NOT NULL).

Here is the statement:

SELECT MAX(length), MIN(length), COUNT(length)
FROM tracks WHERE length IS NOT NULL;

The problem is the value COUNT() returns is the exact same whether I include the IS NOT NULL operator at the end or not. The table has around 25 entries that are NULL so surely I should be getting a lower result for COUNT() when I add the NOT NULL operator to the end of the statement?

Upvotes: 0

Views: 43

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

BACKTICKS are your friend: length is a function name in MySQL and if you want o use it as field name you must put it in backticks like this:

SELECT MAX(`length`), MIN(`length`), COUNT(`length`)
FROM tracks WHERE `length` IS NOT NULL;

Upvotes: 0

Fidel Coria
Fidel Coria

Reputation: 38

The documentation says that count will ignore null columns http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html

Including the where clause will not make a difference.

Upvotes: 1

Related Questions