2

Suppose this data:

id      firstname     surname     val1      val2      val3
1       joe           blogs       1         (null)    (null)
2       jane          doe         (null)    2         5
3       bobby         tables      (null)    (null)    (null)

I'd like a query that would return:

id     count_non_null_vals
1      1
2      2
3      0

COUNT and SUM obviously doesn't work (if they do, educate me!), and whilst we have GREATEST and LEAST as in-row similies to MAX/MIN, I can't find something analagous to COUNT.

I can make a horrible behemoth of nested IFs etc, but in my real-world legacy example there's 30 of these columns, so I'd rather be able to do COL_COUNT(val1, val2, val3... etc

Thank you!

3

Right from the MySQL Documentation

COUNT(expr) [over_clause]

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

If there are no matching rows, COUNT() returns 0.

Just use COUNT() function on each column and add them up last

SELECT
   id,COUNT(val1)+COUNT(val2)+COUNT(val3) count_non_null_vals
FROM mytable;

You can use your PHP / Python / Java to craft the SQL since you have 30 columns.

UPDATE 2021-09-20 11:45 EDT

I decided to actually write this out for real. Here is what I did:

MY PROPOSED QUERY

SET session sql_mode = '';
SELECT IFNULL(id,'') all_ids
,COUNT(val1)+COUNT(val2)+COUNT(val3) count_non_null_vals
FROM mytable GROUP BY id WITH ROLLUP;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS codemonkey;
CREATE DATABASE codemonkey;
USE codemonkey
CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20),
    surname VARCHAR(20),
    val1 INT DEFAULT NULL,
    val2 INT DEFAULT NULL,
    val3 INT DEFAULT NULL,
    PRIMARY KEY (id)
);
INSERT INTO mytable (firstname,surname,val1,val2,val3) VALUES
('joe','blogs',1,NULL,NULL),
('jane','doe',NULL,2,5),
('bobby','tables',NULL,NULL,NULL);
SELECT * FROM mytable;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS codemonkey;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE codemonkey;
Query OK, 1 row affected (0.00 sec)

mysql> USE codemonkey
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     firstname VARCHAR(20),
    ->     surname VARCHAR(20),
    ->     val1 INT DEFAULT NULL,
    ->     val2 INT DEFAULT NULL,
    ->     val3 INT DEFAULT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO mytable (firstname,surname,val1,val2,val3) VALUES
    -> ('joe','blogs',1,NULL,NULL),
    -> ('jane','doe',NULL,2,5),
    -> ('bobby','tables',NULL,NULL,NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

YOUR TABLE

mysql> SELECT * FROM mytable;
+----+-----------+---------+------+------+------+
| id | firstname | surname | val1 | val2 | val3 |
+----+-----------+---------+------+------+------+
|  1 | joe       | blogs   |    1 | NULL | NULL |
|  2 | jane      | doe     | NULL |    2 |    5 |
|  3 | bobby     | tables  | NULL | NULL | NULL |
+----+-----------+---------+------+------+------+
3 rows in set (0.00 sec)

mysql>

MY PROPOSED QUERY EXECUTED

mysql> SET session sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT IFNULL(id,'') all_ids
    -> ,COUNT(val1)+COUNT(val2)+COUNT(val3) count_non_null_vals
    -> FROM mytable GROUP BY id WITH ROLLUP;
+---------+---------------------+
| all_ids | count_non_null_vals |
+---------+---------------------+
| 1       |                   1 |
| 2       |                   2 |
| 3       |                   0 |
|         |                   3 |
+---------+---------------------+
4 rows in set (0.01 sec)

mysql>

BIG CAVEAT

I had to set the session's sql_mode to blank because MySQL does not like doing aggregate queries of this nature. It complains of an invalidate GROUP BY otherwise.

UPDATE 2021-09-20 12:03 EDT

For those running 5.7/8.0, you can disable sql_mode in the session, run the query and reset it back ....

SET @old_sql_mode = @@session.sql_mode;
SET session sql_mode = '';
SELECT IFNULL(id,'') all_ids
,COUNT(val1)+COUNT(val2)+COUNT(val3) count_non_null_vals
FROM mytable GROUP BY id WITH ROLLUP;
SET session sql_mode = @old_sql_mode;
7
  • Nice, thank you, simply summing multiple COUNTs didn't occur to me
    – Codemonkey
    Sep 20 at 15:50
  • I updated my answer with an actual solution Sep 20 at 15:51
  • That IS a big caveat, isn't it. With my SQL mode of STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION I assume I'll be out of luck?
    – Codemonkey
    Sep 20 at 15:57
  • You can set it within the DB Connection before calling the query. Sep 20 at 15:59
  • Let me add to this answer ... Sep 20 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.