BIG_MAC_AND_FRIES February 2016

COUNTNULL() as a MySQL function. How would it operate?

I am sure it would be an aggregate function because it is going to count a collection of data.

However, how does any COUNT() function operate in MySQL to perform its respective actions?

Answers


Brian C February 2016

Not 100% clear what you are looking for, but for selecting a count of null values in a column, I use something like this:

SELECT SUM(CASE WHEN columnname IS NULL THEN 1 ELSE 0 END) FROM tablename;

When the value is NULL, it is assigned the value 1 otherwise 0, then summed over whatever aggregate you need.


spencer7593 February 2016

The COUNT(*) is an aggregate function. In the SELECT list, the expression COUNT(*) will return a count of rows. Without a GROUP BY clause, all rows will be collapsed into a single row, and the COUNT(*) aggregate will contain a non-negative integer value representing the number of rows that were collapsed... a "count" of the number of rows.

As you seem to be aware, other expressions involving the COUNT() aggregate operate a little differently, with respect to NULL values.

In the SELECT list, an expression COUNT(expr) operates exactly like COUNT(*) except for rows with values of expr that evaluate to NULL are not included in the count.

This all operates according to the specification.

As far as the non-existent COUNTNULL() function, it depends what you want that to achieve. If you wanted to get a count of the rows that had a NULL value for an expression, you could perform a conditional test, and return a non-NULL value, and use the existing COUNT aggregate, for example:

SELECT COUNT(CASE WHEN expr IS NULL THEN 1 ELSE NULL END) AS `COUNTNULL`
  FROM ...


Michael - sqlbot February 2016

I don't remember where I learned this technique, but arguably the most elegant -- or at least minimalistic -- way to invert the logic of COUNT() is with this expression, which admittedly gives a first impression that black magic may somehow be involved... but it's perfectly legitimate:

COUNT(column1 IS NULL OR NULL)

...this correctly counts only the rows where column1 is null, because it is equivalent to the following expression...

COUNT( (column1 IS NULL) OR (NULL) )

It's a boolean expression that can only ever evaluate to 1 ("true," when column1 is null, and this row is thus counted), or NULL (otherwise, so the row will not be counted).

Logically, it's equivalent to the CASE expression offered by @spencer7593.

Post Status

Asked in February 2016
Viewed 1,449 times
Voted 11
Answered 3 times

Search




Leave an answer