This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS):

select sum(case when a is null then 1 else 0 end) count_nulls
     , count(a) count_not_nulls 
  from us;

Or:

select count(*) - count(a), count(a) from us;

Try

SELECT 
   DATE_FORMAT(registDate, '%m-%Y') AS month,
   COUNT(name) AS register,
   SUM(!ISNULL(visited)) AS visited,
   SUM(ISNULL(visited)) AS not_visited
FROM mytable
GROUP BY DATE_FORMAT(registDate, '%m-%Y');

No need to create another column.

Hi @Nik,

Please try the following solution as a starting point.

USE tempdb;
GO
DROP TABLE IF EXISTS #tmpTable;
-- DDL and sample data population, start
CREATE TABLE #tmpTable  (ID INT IDENTITY PRIMARY KEY, Col_1 VARCHAR(20), Col_2 VARCHAR(20), Col_3 VARCHAR(20), Col_4 VARCHAR(20));
INSERT INTO #tmpTable (Col_1, Col_2, Col_3, Col_4) VALUES
(NULL, 'Nike', 'Adidas', NULL),
(NULL, 'Nike', 'Adidas', 'Asics'),
(NULL, 'Asics', NULL, 'Asics'),
(NULL, 'Nike', NULL, 'Adidas');
-- DDL and sample data population, end
DECLARE @total_row_counter INT = (SELECT count(*) from tempdb..#tmpTable);
;WITH rs AS
(
	SELECT c.value('local-name(.)', 'sysname') AS column_name
	FROM #tmpTable AS t
	CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE) AS t1(x)
	CROSS APPLY x.nodes('/*') AS t2(c)
), rs2 AS
(
	(
	SELECT name AS column_name, NULL AS row_counter_with_values
	FROM tempdb.sys.columns
	WHERE object_id = object_id('tempdb..#tmpTable')
	   AND is_nullable = 1
	EXCEPT
	SELECT column_name, NULL
	FROM rs
	)
	UNION ALL
	SELECT column_name, COUNT(*) AS row_counter_with_values
	FROM rs
	GROUP BY column_name
)
SELECT * 
	, @total_row_counter - COALESCE(row_counter_with_values,0) AS row_counter_with_nulls
	, @total_row_counter AS total_row_counter 
FROM rs2;

COUNT counts values, since null is not a value it does not get counted.

If you want to count all null values you could do something like this:

SELECT COUNT(ID) as NotNull, SUM(CASE WHEN ID IS NULL then 1 else 0 end) as NullCount

The best way to do this would be to construct the query in one shot using STRING_AGG and sys.columns (you should avoid INFORMATION_SCHEMA, it's for compatibility only).

We start off with a basic query for say: Table1 (Col1, Col2), remember that COUNT(something) only counts up non-null values

SELECT
  TableName = 'Table1',
  TotalRows = COUNT(*),
  Col1 = COUNT(Col1),
  Col2 = COUNT(Col2)
FROM Table1

We then unpivot that:

SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = 'Table1',
      TotalRows = COUNT(*),
      Col1 = COUNT(Col1),
      Col2 = COUNT(Col2)
    FROM Table1
) t
UNPIVOT (NonNullsRows FOR Col IN (
    Col1, Col2
)) p

We can then dynamically construct the above for all tables and columns

DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(TableQuery, N'
UNION ALL
')
    FROM (
        SELECT N'
SELECT
  TableName,
  Col as ColumnName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = ' + QUOTENAME(t.name, '''') + N',
      TotalRows = COUNT(*),
      ' + STRING_AGG(CAST(QUOTENAME(c.name) + ' = COUNT(' + QUOTENAME(c.name) + ')' AS nvarchar(max)), ',') + N'
    FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + N'
) t
UNPIVOT (NonNullsRows FOR Col IN 
    (' + STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), ',') + N'
)) p'

        FROM sys.tables t
        JOIN sys.columns c ON c.object_id = t.object_id
        WHERE c.name LIKE 'new_'   -- do you want this filter
           -- you may also want AND c.is_nullable = 1
        GROUP BY t.object_id, t.name, t.schema_id

    ) AS t(TableQuery)
);

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;

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;