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

Question

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?

Fidel Coria · Accepted Answer · 2016-12-03 22:04:06Z

1

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.

answered Dec 3, 2016 at 22:04

Fidel Coria

385 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael - sqlbot Over a year ago

This is correct. If you want to count all rows matched by WHERE, use COUNT(*) or COUNT(1) (they are equivalent). If you want to count rows where col1 is not null, that's exactly what COUNT(col1) does.

Bernd Buffen · Accepted Answer · 2016-12-03 22:06:25Z

0

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;

answered Dec 3, 2016 at 22:06

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related