Count number of null and not nulls in table

December 1, 2022

Here is my query. My table has only 4 rows right now. 3 of them have a UNIX timestamp in column confirmed_at, and 1 row is null

SELECT 
   date(`user`.`created_at`) AS `Date`, 
   SUM(case `user`.`confirmed_at` when null then 1 else 0 end) AS 'Null values',
   SUM(case `user`.`confirmed_at` when null then 0 else 1 end) AS 'Non-null values'
FROM `user`
GROUP BY date(`user`.`created_at`);

When i run this query, results shown are

Date      | Null Values | non-null values
1/12/2022 | 0           | 4

it should be

Date      | Null Values | non-null values
1/12/2022 | 1           | 3

can someone help me, not sure what i’m missing here. Thank you.

>Solution :

You can try by adding IS when check if it’s null or not,more details can be found at t-sql-case-clause-how-to-specify-when-null

SELECT 
   date(`user`.`created_at`) AS `Date`, 
   SUM(case `user`.`confirmed_at` when is null then 1 else 0 end) AS 'Null values',
   SUM(case `user`.`confirmed_at` when is null then 0 else 1 end) AS 'Non-null values'
FROM `user`
GROUP BY date(`user`.`created_at`);