逐行计算，不包括空值 [英] cloumnwise rowcount excluding null values

问题描述

朋友们，
请帮帮我..
我想在单个查询中按列进行行计数(不包括null值)..
请参见以下示例:
table1:

Hi friends,
Please help me..
i want columnwise rowcount(excluding null values) in single query..
see the below example:
table1:

------------------------------
col1  |   col2      |   col3
------------------------------
 1    |  null       |   test
 2    |  1w         |   test1
 null |  null       |   test3 

------------------------------

输出将是:

------------------------------

output will be:

----------------------
columnname | count
-----------------
col1       | 2
col2       | 1
col3       | 3
------------------

对于上面的输出，我已经尝试了以下sql查询:-

for above output i have tried below sql query:-

select a.name ,(select count(a.name) from users b where a.name is not null)
from syscolumns a where id=(select id from sysobjects where name='users')
group by a.name

但这给了我所有表rowcount像-

but it is giving me all tables rowcount like-

col1 |3
col2 |3
col3 |3

:(


在此先感谢...

:(


Thanks in advance...

推荐答案

尝试一下

SELECT COUNT(COL1) AS COL1 ,COUNT(COL2) AS COL2 ,COUNT(COL3) AS COL3 FROM USER 

无需编写不为null的字符串，因为count()函数不会计算该null值.

No need to write is not null because count() function does not count the null value.

有关MSDN上Count的更多信息: ^ ]

More about Count at MSDN: Count (Transact-SQL)[^]

这篇关于逐行计算，不包括空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！