我有一张桌子
create table us
(
a number
);
现在我有如下数据:
a
1
2
3
4
null
null
null
8
9
现在我需要一个查询来计算列a中的空值和非空值
我有一张桌子
create table us
(
a number
);
现在我有如下数据:
a
1
2
3
4
null
null
null
8
9
现在我需要一个查询来计算列a中的空值和非空值
当前回答
select count(isnull(NullableColumn,-1))
其他回答
如果我理解正确,你想在一个列中计数所有NULL和所有NOT NULL…
如果是正确的:
SELECT count(*) FROM us WHERE a IS NULL
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL
阅读评论后,编辑了完整的查询:]
SELECT COUNT(*), 'null_tally' AS narrative
FROM us
WHERE a IS NULL
UNION
SELECT COUNT(*), 'not_null_tally' AS narrative
FROM us
WHERE a IS NOT NULL;
以防你想把它记录在一条记录里:
select
(select count(*) from tbl where colName is null) Nulls,
(select count(*) from tbl where colName is not null) NonNulls
;-)
这里有两种解决方案:
Select count(columnname) as countofNotNulls, count(isnull(columnname,1))-count(columnname) AS Countofnulls from table name
OR
Select count(columnname) as countofNotNulls, count(*)-count(columnname) AS Countofnulls from table name
这在T-SQL中有效。如果你只是计算一些东西的数量,你想包括空值,使用COALESCE而不是case。
IF OBJECT_ID('tempdb..#us') IS NOT NULL
DROP TABLE #us
CREATE TABLE #us
(
a INT NULL
);
INSERT INTO #us VALUES (1),(2),(3),(4),(NULL),(NULL),(NULL),(8),(9)
SELECT * FROM #us
SELECT CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END AS 'NULL?',
COUNT(CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END) AS 'Count'
FROM #us
GROUP BY CASE WHEN a IS NULL THEN 'NULL' ELSE 'NON-NULL' END
SELECT COALESCE(CAST(a AS NVARCHAR),'NULL') AS a,
COUNT(COALESCE(CAST(a AS NVARCHAR),'NULL')) AS 'Count'
FROM #us
GROUP BY COALESCE(CAST(a AS NVARCHAR),'NULL')
为了提供另一种选择,Postgres 9.4+允许对聚合应用FILTER:
SELECT
COUNT(*) FILTER (WHERE a IS NULL) count_nulls,
COUNT(*) FILTER (WHERE a IS NOT NULL) count_not_nulls
FROM us;
SQLFiddle: http://sqlfiddle.com/ # !17/80a24/5