如何计算SQL中的非空/非空值

I have data like the following:

我有如下数据:

Data http://i46.tinypic.com/23t5m2u.png

http://i46.tinypic.com/23t5m2u.png的数据

And what I want is to count the PONo, PartNo, and TrinityID fields with a value in them, and output data like this:

我想要的是计算PONo、PartNo和TrinityID字段的值，并输出如下数据:

Desired Output http://i50.tinypic.com/nvodw.png

期望输出值http://i50.tinypic.com/nvodw.png

How can I do this counting in SQL?

如何在SQL中进行计数?

3 个解决方案

#1

select 
 Job_number, Item_code,
 case when RTRIM(PONo) = '' or PONo is null then 0 else 1 end +
 case when RTRIM(PartNo) = '' or PartNo is null then 0 else 1 end +
 case when RTRIM(TrinityID) = '' or TrinityID is null then 0 else 1 end 
 as [Count]
from YourTable

#2

Try this:

试试这个:

select Job_Number = t.Job_Number ,
       Item_Code  = t.Item_Code  ,
       "Count"    = sum( case ltrim(rtrim(coalesce( PONo      , '' ))) when '' then 0 else 1 end
                       + case ltrim(rtrim(coalesce( PartNo    , '' ))) when '' then 0 else 1 end
                       + case ltrim(rtrim(coalesce( TrinityID , '' ))) when '' then 0 else 1 end
                       )
from dbo.my_table t
group by t.Job_Number , t.Item_Code

If you want to exclude data where all the tested fields are null or empty, add a having clause:

如果您想要排除所有测试字段为null或空的数据，请添加一个having子句:

having sum( case ltrim(rtrim(coalesce( PONo      , '' ))) when '' then 0 else 1 end
          + case ltrim(rtrim(coalesce( PartNo    , '' ))) when '' then 0 else 1 end
          + case ltrim(rtrim(coalesce( TrinityID , '' ))) when '' then 0 else 1 end
          ) > 0

Easy!

简单!

#3

And another

和另一个

Select Job_number, Item_code, Sum([Count]) as [Count] From
(
Select Job_number,Item_Code, 1 as [Count] Where PONO is not null and PONO <> ''
Union all
Select Job_number,Item_Code, 1 Where PartNo IS not Null and PartNo <> ''
Union all
Select Job_number,Item_Code, 1 Where TrinityID is not null and TrinityID <> ''
) allthree
Group by job_number, Item_code