How to count the column

Question

Table1' Sample data:

ID value1 Value2 value3

001 10 20 30
002 20 10 null
003 10 null null
004 10 null 30
....

From the table1, I want to make a coulmn count.

Count of row query

Select count(*) from table1 'It will give only row count.

But i need column count, which column value should not be null

Expected output

 ID   | totcolumn
-----------------
 001     3
 002     2
 003     1
 004     2
....

How to make a query, need query help

Answer 1

Use SUM as follows:

SELECT id,
       SUM(CASE WHEN value1 IS NULL then 0 ELSE 1 END) +
       SUM(CASE WHEN value2 IS NULL then 0 ELSE 1 END) +
       SUM(CASE WHEN value3 IS NULL then 0 ELSE 1 END) AS 'COUNT'
FROM table1
group by id

DEMO