Skip to content
Advertisement

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?

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement