SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Count Columns That Contain Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 03/09/2014 :  09:47:14  Show Profile  Reply with Quote
hi

How do I count numbers of columns that has value?

For example

ColA ColB ColC ColD ColE ColF
12 10.5 50 32 NULL NULL

In this example, I should have a count of 4

How should go about doing it?

many thanks.

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 03/09/2014 :  09:50:32  Show Profile  Reply with Quote
CASE WHEN ColA IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN ColB IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN ColC IS NOT NULL THEN 1 ELSE 0 END
+ CASE WHEN ColD IS NOT NULL THEN 1 ELSE 0 END
+ . . .



KH
Time is always against us

Go to Top of Page

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 03/09/2014 :  10:05:56  Show Profile  Reply with Quote
hi

My apology, but how do I add up all the 1? Many thanks
Go to Top of Page

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 03/09/2014 :  10:06:49  Show Profile  Reply with Quote
sorry I didn't see the + sign. I thought it is a concat. Thanks

I got it now. Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000