CODEWITHSUNDEEP
sqlsql-serversql-server-2000
MAW74656
MAW74656

Reputation: 3539

How to count non-null/non-blank values in SQL

I have data like the following:

Data

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

Desired Output

How can I do this counting in SQL?

Upvotes: 4

Views: 11075

Answers (2)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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

Upvotes: 5

Nicholas Carey
Nicholas Carey

Reputation: 74267

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:

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!

Upvotes: 1

Related Questions