blocks|key|3096182|text|这适用于Oracle和SQL+Server+(您也许能够让它在另一个RDBMS上工作)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096183|select+sum(case+when+a+is+null+then+1+else+0+end)+count_nulls
+++++,+count(a)+count_not_nulls+
++from+us;|code-block|syntax|javascript|3096184|或者：|3096185|select+count(*)+-+count(a),+count(a)+from+us;|3096186|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This works for Oracle and SQL Server (you might be able to get it to work on another RDBMS):

<pre><code>select sum(case when a is null then 1 else 0 end) count_nulls
 , count(a) count_not_nulls 
 from us;
</code></pre>

Or:

<pre><code>select count(*) - count(a), count(a) from us;
</code></pre>

blocks|key|334356|text|如果我理解正确的话，您希望在一列中计算所有NULL和all+NOT+NULL+...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334357|如果这是正确的：|334358|SELECT+count(*)+FROM+us+WHERE+a+IS+NULL+
UNION+ALL
SELECT+count(*)+FROM+us+WHERE+a+IS+NOT+NULL|code-block|syntax|javascript|334359|在阅读评论后，编辑以获得完整的查询:]|334360|334361|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;|334362|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|R|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|S|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|L|3|M|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|N|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|O|$]]

If I understood correctly you want to count all NULL and all NOT NULL in a column...

If that is correct:

<pre><code>SELECT count(*) FROM us WHERE a IS NULL 
UNION ALL
SELECT count(*) FROM us WHERE a IS NOT NULL
</code></pre>

Edited to have the full query, after reading the comments :]

<hr>

<pre><code>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;
</code></pre>

blocks|key|3096117|text|下面是一个在Oracle上运行的快速而粗糙的版本：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096118|select+sum(case+a+when+null+then+1+else+0)+"Null+values",
+++++++sum(case+a+when+null+then+0+else+1)+"Non-null+values"
from+us|code-block|syntax|javascript|3096119|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a quick and dirty version that works on Oracle :

<pre><code>select sum(case a when null then 1 else 0) "Null values",
 sum(case a when null then 0 else 1) "Non-null values"
from us
</code></pre>

blocks|key|331614|text|对于非空值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|331615|select+count(a)
from+us|code-block|syntax|javascript|331616|对于空值|331617|select+count(*)
from+us

minus+

select+count(a)
from+us|331618|因此|331619|SELECT+COUNT(A)+NOT_NULLS
FROM+US

UNION

SELECT+COUNT(*)+-+COUNT(A)+NULLS
FROM+US|331620|应该做好这项工作|331621|更好的是，专栏的标题是正确的。|331622|SELECT+COUNT(A)+NOT_NULL,+COUNT(*)+-+COUNT(A)+NULLS
FROM+US|331623|在我的系统上的一些测试中，它需要全表扫描。|331624|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

for non nulls

<pre><code>select count(a)
from us
</code></pre>

for nulls

<pre><code>select count(*)
from us

minus 

select count(a)
from us
</code></pre>

Hence

<pre><code>SELECT COUNT(A) NOT_NULLS
FROM US

UNION

SELECT COUNT(*) - COUNT(A) NULLS
FROM US
</code></pre>

ought to do the job

Better in that the column titles come out correct.

<pre><code>SELECT COUNT(A) NOT_NULL, COUNT(*) - COUNT(A) NULLS
FROM US
</code></pre>

In some testing on my system, it costs a full table scan.

blocks|key|336813|text|根据我对您查询的理解，您只需运行此脚本，即可获得Total+Null、Total+NotNull行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336814|select+count(*)+-+count(a)+as+'Null',+count(a)+as+'Not+Null'+from+us;|code-block|syntax|javascript|336815|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

As i understood your query, You just run this script and get Total Null,Total NotNull rows,

<pre><code>select count(*) - count(a) as 'Null', count(a) as 'Not Null' from us;
</code></pre>

blocks|key|331768|text|通常我会用这个技巧|type|unstyled|depth|inlineStyleRanges|entityRanges|data|331769|select+sum(case+when+a+is+null+then+0+else+1+end)+as+count_notnull,
+++++++sum(case+when+a+is+null+then+1+else+0+end)+as+count_null
from+tab
group+by+a|code-block|syntax|javascript|331770|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

usually i use this trick

<pre><code>select sum(case when a is null then 0 else 1 end) as count_notnull,
 sum(case when a is null then 1 else 0 end) as count_null
from tab
group by a
</code></pre>

blocks|key|3096513|text|只是为了提供另一种选择，Postgres+9.4%2B+allows+applying+a+FILTER+to+aggregates|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3096514|SELECT
++COUNT(*)+FILTER+(WHERE+a+IS+NULL)+count_nulls,
++COUNT(*)+FILTER+(WHERE+a+IS+NOT+NULL)+count_not_nulls
FROM+us;|code-block|syntax|javascript|3096515|SQLFiddle：http://sqlfiddle.com/#!17/80a24/5|3096516|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES|1|http://sqlfiddle.com/#!17/80a24/5^0|18|6|Q|12|0|0|0|A|X|1|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]|U|$5|P|Q|R|E|$S|V]]]]

Just to provide yet another alternative, Postgres 9.4+ <a href="https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES" rel="noreferrer">allows applying a <code>FILTER</code> to aggregates</a>:

<pre><code>SELECT
 COUNT(*) FILTER (WHERE a IS NULL) count_nulls,
 COUNT(*) FILTER (WHERE a IS NOT NULL) count_not_nulls
FROM us;
</code></pre>

SQLFiddle: <a href="http://sqlfiddle.com/#!17/80a24/5" rel="noreferrer">http://sqlfiddle.com/#!17/80a24/5</a>

blocks|key|3096483|text|这有点棘手。假设表只有一列，那么Count(1)和Count(*)将给出不同的值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096484|set+nocount+on
++++declare+@table1+table+(empid+int)
++++insert+@table1+values+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(NULL),(11),(12),(NULL),(13),(14);

++++select+*+from+@table1
++++select+COUNT(1)+as+"COUNT(1)"+from+@table1
++++select+COUNT(empid)+"Count(empid)"+from+@table1|code-block|syntax|javascript|3096485|Query+Results|offset|length|3096486|正如您在图像中看到的，第一个结果显示该表有16行。其中有两行为NULL。因此，当我们使用+count+(*)时，查询引擎对行数进行计数，因此我们得到的计数结果为16。但是，如果使用Count(+empid+)，它会对列empid中的非空值进行计数。所以我们得到的结果是14。|style|BOLD|3096487|因此，当我们使用COUNT(Column)时，一定要注意null值，如下所示。|3096488|select+COUNT(isnull(empid,1))+from+@table1|3096489|将同时计算空值和非空值。|3096490|注意：：即使表由多个列组成，情况也是如此。Count(1)将给出总行数，而不考虑NULL/非NULL值。只有当使用Count(+column+)对列值进行计数时，我们才需要注意空值。|3096491|entityMap|0|LINK|mutability|MUTABLE|url|https://i.stack.imgur.com/DLH2y.png^0|0|0|0|D|0|0|18|A|2I|E|0|0|0|0|0|3|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|16|8|@]|9|@$I|17|J|18|1|19]]|A|$]]|$1|K|3|L|5|6|7|1A|8|@$I|1B|J|1C|M|N]|$I|1D|J|1E|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1G|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1H|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1I|8|@$I|1J|J|1K|M|N]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

This is little tricky. Assume the table has just one column, then the Count(1) and Count(*) will give different values.

<pre><code>set nocount on
 declare @table1 table (empid int)
 insert @table1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(NULL),(11),(12),(NULL),(13),(14);

 select * from @table1
 select COUNT(1) as "COUNT(1)" from @table1
 select COUNT(empid) "Count(empid)" from @table1
</code></pre>

<a href="https://i.stack.imgur.com/DLH2y.png" rel="noreferrer">Query Results</a>

As you can see in the image, The first result shows the table has 16 rows. out of which two rows are NULL. So when we use Count(*) the query engine counts the number of rows, So we got count result as 16. But in case of Count(empid) it counted the non-NULL-values in the column empid. So we got the result as 14. 

so whenever we are using COUNT(Column) make sure we take care of NULL values as shown below.

<pre><code>select COUNT(isnull(empid,1)) from @table1
</code></pre>

will count both NULL and Non-NULL values.

Note: Same thing applies even when the table is made up of more than one column. Count(1) will give total number of rows irrespective of NULL/Non-NULL values. Only when the column values are counted using Count(Column) we need to take care of NULL values.

blocks|key|333174|text|我有一个类似的问题:计算所有不同的值，也将空值算作1。在这种情况下，简单的计数不起作用，因为它不考虑空值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|333175|下面是一个在SQL上工作的代码片段，它不涉及新值的选择。基本上，一旦执行了distinct，还会使用row_number()函数返回新列(n)中的行号，然后对该列执行计数：|333176|SELECT+COUNT(n)
FROM+(
++++SELECT+*,+row_number()+OVER+(ORDER+BY+[MyColumn]+ASC)+n
++++FROM+(
++++++++SELECT+DISTINCT+[MyColumn]
++++++++++++++++++++FROM+[MyTable]
++++++++)+items++
)+distinctItems|code-block|syntax|javascript|333177|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I had a similar issue: to count all distinct values, counting null values as 1, too. A simple count doesn't work in this case, as it does not take null values into account.

Here's a snippet that works on SQL and does not involve selection of new values.
Basically, once performed the distinct, also return the row number in a new column (n) using the row_number() function, then perform a count on that column:

<pre><code>SELECT COUNT(n)
FROM (
 SELECT *, row_number() OVER (ORDER BY [MyColumn] ASC) n
 FROM (
 SELECT DISTINCT [MyColumn]
 FROM [MyTable]
 ) items 
) distinctItems
</code></pre>

blocks|key|3309048|text|这里有两个解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3309049|Select+count(columnname)+as+countofNotNulls,+count(isnull(columnname,1))-count(columnname)+AS+Countofnulls+from+table+name|code-block|syntax|javascript|3309050|或|3309051|Select+count(columnname)+as+countofNotNulls,+count(*)-count(columnname)+AS+Countofnulls+from+table+name|3309052|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here are two solutions:

<pre><code>Select count(columnname) as countofNotNulls, count(isnull(columnname,1))-count(columnname) AS Countofnulls from table name
</code></pre>

OR

<pre><code>Select count(columnname) as countofNotNulls, count(*)-count(columnname) AS Countofnulls from table name
</code></pre>

blocks|key|334404|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334405|SELECT+
+++SUM(ISNULL(a))+AS+all_null,
+++SUM(!ISNULL(a))+AS+all_not_null
FROM+us;|code-block|syntax|javascript|334406|很简单！|334407|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try

<pre><code>SELECT 
 SUM(ISNULL(a)) AS all_null,
 SUM(!ISNULL(a)) AS all_not_null
FROM us;
</code></pre>

Simple!

blocks|key|3309385|text|试试这个..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3309386|SELECT+CASE+
+++++++++WHEN+a+IS+NULL+THEN+'Null'+
+++++++++ELSE+'Not+Null'+
+++++++END+a,+
+++++++Count(1)+
FROM+++us+
GROUP++BY+CASE+
++++++++++++WHEN+a+IS+NULL+THEN+'Null'+
++++++++++++ELSE+'Not+Null'+
++++++++++END+|code-block|syntax|javascript|3309387|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this..

<pre><code>SELECT CASE 
 WHEN a IS NULL THEN 'Null' 
 ELSE 'Not Null' 
 END a, 
 Count(1) 
FROM us 
GROUP BY CASE 
 WHEN a IS NULL THEN 'Null' 
 ELSE 'Not Null' 
 END 
</code></pre>

blocks|key|3096192|text|如果您使用的是MS+Sql+Server...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096193|SELECT+COUNT(0)+AS+'Null_ColumnA_Records',
(
++++SELECT+COUNT(0)
++++FROM+your_table
++++WHERE+ColumnA+IS+NOT+NULL
)+AS+'NOT_Null_ColumnA_Records'
FROM+your_table
WHERE+ColumnA+IS+NULL;|code-block|syntax|javascript|3096194|我不建议你这么做...但是在这里你有它(在与结果相同的表中)|3096195|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you're using MS Sql Server... 

<pre><code>SELECT COUNT(0) AS 'Null_ColumnA_Records',
(
 SELECT COUNT(0)
 FROM your_table
 WHERE ColumnA IS NOT NULL
) AS 'NOT_Null_ColumnA_Records'
FROM your_table
WHERE ColumnA IS NULL;
</code></pre>

I don't recomend you doing this... but here you have it (in the same table as result)

blocks|key|334380|text|使用ISNULL嵌入式函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334381|334382|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|E|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|F|8|@]|9|@]|A|$]]|$1|C|3|-4|5|6|7|G|8|@]|9|@]|A|$]]]|D|$]]

use ISNULL embedded function.

<hr>

blocks|key|333142|text|SELECT+SUM(NULLs)+AS+'NULLS',+SUM(NOTNULLs)+AS+'NOTNULLs'+FROM+
++++(select+count(*)+AS+'NULLs',+0+as+'NOTNULLs'+FROM+us+WHERE+a+is+null
++++UNION+select+0+as+'NULLs',+count(*)+AS+'NOTNULLs'+FROM+us+WHERE+a+is+not+null)+AS+x|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|333143|它是模糊的，但它将返回一个包含2个参数的记录，指示空值与非空值的计数。|unstyled|333144|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT SUM(NULLs) AS 'NULLS', SUM(NOTNULLs) AS 'NOTNULLs' FROM 
 (select count(*) AS 'NULLs', 0 as 'NOTNULLs' FROM us WHERE a is null
 UNION select 0 as 'NULLs', count(*) AS 'NOTNULLs' FROM us WHERE a is not null) AS x
</code></pre>

It's fugly, but it will return a single record with 2 cols indicating the count of nulls vs non nulls.

blocks|key|336728|text|select+count(isnull(NullableColumn,-1))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|336729|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select count(isnull(NullableColumn,-1))
</code></pre>

blocks|key|334448|text|这在T-SQL中有效。如果你只是在计算一些东西的数量，并且你想包括空值，那么使用COALESCE而不是case。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|334449|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')|code-block|syntax|javascript|334450|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works in T-SQL. If you're just counting the number of something and you want to include the nulls, use COALESCE instead of case.

<pre><code>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')
</code></pre>

blocks|key|336803|text|在Alberto的基础上，我添加了汇总。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336804|+SELECT+[Narrative]+=+CASE+
+WHEN+[Narrative]+IS+NULL+THEN+'count_total'+ELSE++++[Narrative]+END
,[Count]=SUM([Count])+FROM+(SELECT+COUNT(*)+[Count],+'count_nulls'+AS+[Narrative]++
FROM+[CrmDW].[CRM].[User]++
WHERE+[EmployeeID]+IS+NULL+
UNION
SELECT+COUNT(*),+'count_not_nulls+'+AS+narrative+
FROM+[CrmDW].[CRM].[User]+
WHERE+[EmployeeID]+IS+NOT+NULL)+S+
GROUP+BY+[Narrative]+WITH+CUBE;|code-block|syntax|javascript|336805|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Building off of Alberto, I added the rollup.

<pre><code> SELECT [Narrative] = CASE 
 WHEN [Narrative] IS NULL THEN 'count_total' ELSE [Narrative] END
,[Count]=SUM([Count]) FROM (SELECT COUNT(*) [Count], 'count_nulls' AS [Narrative] 
FROM [CrmDW].[CRM].[User] 
WHERE [EmployeeID] IS NULL 
UNION
SELECT COUNT(*), 'count_not_nulls ' AS narrative 
FROM [CrmDW].[CRM].[User] 
WHERE [EmployeeID] IS NOT NULL) S 
GROUP BY [Narrative] WITH CUBE;
</code></pre>

blocks|key|334504|text|SELECT
++++ALL_VALUES
++++,COUNT(ALL_VALUES)
FROM(
++++++++SELECT+
++++++++NVL2(A,'NOT+NULL','NULL')+AS+ALL_VALUES+
++++++++,NVL(A,0)
++++++++FROM+US
)
GROUP+BY+ALL_VALUES|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|334505|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT
 ALL_VALUES
 ,COUNT(ALL_VALUES)
FROM(
 SELECT 
 NVL2(A,'NOT NULL','NULL') AS ALL_VALUES 
 ,NVL(A,0)
 FROM US
)
GROUP BY ALL_VALUES
</code></pre>

blocks|key|336850|text|所有的答案要么是错误的，要么是非常过时的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336851|执行此查询的简单且正确的方法是使用COUNT_IF函数。|offset|length|style|CODE|336852|SELECT
++COUNT_IF(a+IS+NULL)+AS+nulls,
++COUNT_IF(a+IS+NOT+NULL)+AS+not_nulls
FROM
++us|code-block|syntax|javascript|336853|entityMap^0|0|H|8|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

All the answers are either wrong or extremely out of date.

The simple and correct way of doing this query is using <code>COUNT_IF</code> function.

<pre><code>SELECT
 COUNT_IF(a IS NULL) AS nulls,
 COUNT_IF(a IS NOT NULL) AS not_nulls
FROM
 us
</code></pre>

blocks|key|3096530|text|我在postgres+10中创建了这个表，并且以下两种方法都有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096531|select+count(*)+from+us|offset|length|style|CODE|3096532|和|3096533|select+count(a+is+null)+from+us|3096534|entityMap^0|0|0|N|0|0|0|V|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

I created the table in postgres 10 and both of the following worked:

<code>select count(*) from us</code>

and

<code>select count(a is null) from us</code>

blocks|key|333104|text|如果是mysql，你可以尝试这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|333105|select+
+++(select+count(*)+from+TABLENAME+WHERE+a+=+'null')+as+total_null,+
+++(select+count(*)+from+TABLENAME+WHERE+a+!=+'null')+as+total_not_null
FROM+TABLENAME|code-block|syntax|javascript|333106|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

if its mysql, you can try something like this.

<pre><code>select 
 (select count(*) from TABLENAME WHERE a = 'null') as total_null, 
 (select count(*) from TABLENAME WHERE a != 'null') as total_not_null
FROM TABLENAME
</code></pre>

blocks|key|336723|text|以防万一你想把它放在一条记录中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336724|select+
++(select+count(*)+from+tbl+where+colName+is+null)+Nulls,
++(select+count(*)+from+tbl+where+colName+is+not+null)+NonNulls+|code-block|syntax|javascript|336725|;-)|336726|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Just in case you wanted it in a single record:

<pre><code>select 
 (select count(*) from tbl where colName is null) Nulls,
 (select count(*) from tbl where colName is not null) NonNulls 
</code></pre>

;-)

blocks|key|336752|text|用于计算非空值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336753|select+count(*)+from+us+where+a+is+not+null;|code-block|syntax|javascript|336754|用于计算空值|336755|+select+count(*)+from+us+where+a+is+null;|336756|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

for counting not null values

<pre><code>select count(*) from us where a is not null;
</code></pre>

for counting null values

<pre><code> select count(*) from us where a is null;
</code></pre>

blocks|key|3309391|text|在我的例子中，我想要多个列中的"null分布“：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3309392|SELECT
+++++++(CASE+WHEN+a+IS+NULL+THEN+'NULL'+ELSE+'NOT-NULL'+END)+AS+a_null,
+++++++(CASE+WHEN+b+IS+NULL+THEN+'NULL'+ELSE+'NOT-NULL'+END)+AS+b_null,
+++++++(CASE+WHEN+c+IS+NULL+THEN+'NULL'+ELSE+'NOT-NULL'+END)+AS+c_null,
+++++++...
+++++++count(*)
FROM+us
GROUP+BY+1,+2,+3,...
ORDER+BY+1,+2,+3,...|code-block|syntax|javascript|3309393|根据“...”它很容易扩展到更多的列，根据需要扩展到任意多个列|3309394|entityMap^0|G|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

In my case I wanted the "null distribution" amongst multiple columns:

<pre><code>SELECT
 (CASE WHEN a IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS a_null,
 (CASE WHEN b IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS b_null,
 (CASE WHEN c IS NULL THEN 'NULL' ELSE 'NOT-NULL' END) AS c_null,
 ...
 count(*)
FROM us
GROUP BY 1, 2, 3,...
ORDER BY 1, 2, 3,...
</code></pre>

As per the '...' it is easily extendable to more columns, as many as needed

blocks|key|3308883|text|A为null的元素数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3308884|select+count(a)+from+us+where+a+is+null;|code-block|syntax|javascript|3308885|A不为空的元素数：|3308886|select+count(a)+from+us+where+a+is+not+null;|3308887|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Number of elements where a is null:

<pre><code>select count(a) from us where a is null;
</code></pre>

Number of elements where a is not null:

<pre><code>select count(a) from us where a is not null;
</code></pre>

I have a table 

<pre><code>create table us
(
 a number
);
</code></pre>

Now I have data like:

<pre><code>a
1
2
3
4
null
null
null
8
9
</code></pre>

Now I need a single query to count null and not null values in column a

Counting null and non-null values in a single query

SQL Server

我有一张桌子create table us( a number);现在我有了如下数据：a1234nullnullnull89现在我需要一个查询来计算a列中的null 和 not NULL值

问在单个查询中计算null和非NULL值
EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在单个查询中计算null和非NULL值EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在单个查询中计算null和非NULL值
EN