Count number of null and not nulls in table-CodePudding

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.

CodePudding user response：

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`);