我有这个问题
select count(*) as COUNT_A, datepart(yyyy, [Tgl_Perolehan]) as [year]
from [table_name]
where Sertifikat_Nomor IS NOT NULL
group by datepart(yyyy, [Tgl_Perolehan])
还有这个
select count(*) as COUNT_B, datepart(yyyy, [Tgl_Perolehan]) as [year]
from [table_name]
where Sertifikat_Nomor IS NULL
group by datepart(yyyy, [Tgl_Perolehan])
区别仅在于IS NOT NULL和IS NULL，结果是
+---------+--------+
| COUNT_A | year |
+----------+--------+
| 12 | 1991 |
| 15 | 1993 |
| 24 | 1998 |
+----------+--------+
我想要这样做
+---------+---------+--------+
| COUNT_A | COUNT_B | year |
+----------+---------+--------+
| 12 | 23 | 1991 |
| 15 | 33 | 1993 |
| 24 | 13 | 1998 |
+----------+---------+--------+
我试着这样
select (select count(*) as COUNT_A, datepart(yyyy, [Tgl_Perolehan]) as [year]
from [table_name]
where Sertifikat_Nomor IS NOT NULL
group by datepart(yyyy, [Tgl_Perolehan])),
(select count(*) as COUNT_B, datepart(yyyy, [Tgl_Perolehan]) as [year]
from [table_name]
where Sertifikat_Nomor IS NULL
group by datepart(yyyy, [Tgl_Perolehan]))
但没有解决我的问题。
我使用CASE WHEN，但仍然没有解决问题。
如何合并和修复他们的？
谢谢。
分析解答
试试 this-
SELECT
SUM(CASE WHEN ertifikat_Nomor IS NOT NULL THEN 1 ELSE 0 END) AS COUNT_A,
SUM(CASE WHEN Sertifikat_Nomor IS NULL THEN 1 ELSE 0 END)AS COUNT_B,
DATEPART(yyyy, [Tgl_Perolehan]) AS [year]
FROM[table_name]
GROUP BY DATEPART(yyyy, [Tgl_Perolehan])