在％的表中找不到非空列 [英] finding no of non empty columns from a table in %

问题描述

你好朋友，

我陷入了一个问题，不确定如何编写查询，该查询将以百分比返回以用户ID为基础的行中为空的字段数.

例如，我表中的一行:
第1行:field1 field2 field3

如果field3为空或为null，则我的查询应返回67％.

到目前为止，我已经获得了字段数:
从information_schema.columns中选择count(1)，其中table_name =''myTable''
我正在使用MS SQL2008.
在此先感谢朋友

Hello friends,

I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null on the base of a user''s id.

For instance, a row from my table:
Row1 : field1 field2 field3

If field3 is empty or null, my query should return 67%.

So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name=''myTable''
I am using MS SQL 2008.
Thanks in avance friends

推荐答案

尝试:

SELECT 
   (SELECT (COUNT(1)*100) from myTable where ISNULL(myColumn,'')='') 
   / 
   (SELECT COUNT(1) FROM myTable)

谢谢您的答复，但是我需要计算一行中的所有列(具有值的列)并以％表示"

您最初的问题要求:
以百分比的形式返回行中为空的字段数"

如果您有6行，​​其中两行为空，则上面的查询将返回"33"-即33％或1/3
要对其进行反转并返回不为null的百分比，请在ISNULL前面放置一个"NOT":

"Thanks sir for your reply, but i need to count all the columns(columns having value) of a single row and show it in %"

Your original question asked for:
"return as a percentage the number of fields in a row that are null"

If you have 6 rows, with null in two of them, the query above will return "33" - ie, 33% or 1/3
To invert it, and return the percentage that are not null, put a "NOT" in front of the ISNULL:

SELECT 
   (SELECT (COUNT(1)*100) from myTable where NOT ISNULL(myColumn,'')='') 
   / 
   (SELECT COUNT(1) FROM myTable)

这篇关于在％的表中找不到非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！