計算列中的空值和非空值

如何在 MySQL 的同一列上計算和檢索 null 和 not null？

我的表

---------------------------------------------------
id   |    name    |      visited   |   registDate |
---------------------------------------------------
1    |    george  |       NULL     |   2014-04-01 |
---------------------------------------------------
2    |    Thomas  |       NULL     |   2014-04-15 |
---------------------------------------------------
3    |    Wilfred |        1       |   2014-04-24 |
---------------------------------------------------
4    |    paul    |        1       |   2014-04-10 |
---------------------------------------------------
5    |    elina   |       NULL     |   2014-05-03 |
---------------------------------------------------
6    |    angela  |       NULL     |   2014-04-13 |
---------------------------------------------------
7    |    elina   |        1       |   2014-05-18 |
---------------------------------------------------

預期結果

month      register    visited    not visited
---------------------------------------------
05-2014       2           1          1   
---------------------------------------------
04-2014       5           2          3
---------------------------------------------

嘗試

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

無需創建另一列。

首先要做的是為該月“添加”一列：

select *, date_format(registDate, '%Y-%m') as regist_month
from mytable

然後你可以得到所有的計數：

select
 regist_month
, count(registDate) as count_registered
, sum(case when visited is not null then 1 else 0 end) as count_visited
, sum(case when visited is null then 1 else 0 end) as count_not_visited
from (
 select *, date_format(registDate, '%Y-%m') as regist_month
 from mytable
) group by regist_month

引用自：https://dba.stackexchange.com/questions/64927