Get number of columns with data

I want to write a query that will tell me how many columns have data in it for each row.  

My columns are ID, DE6, DE16, DE26, DE36, DE46, DE56, DE66, DE76, DE86, DE96, DE106, DE116, DE126, DE136.

Thanks.
pansophyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
select count(*) from table where id is not null
union
select count(*) from table where de6 is not null
union
... (etc)
jdlambert1Commented:
SELECT CASE WHEN ID IS NOT NULL THEN 1 ELSE 0 END +
 CASE WHEN DE6 IS NOT NULL THEN 1 ELSE 0 END cnt +
 CASE WHEN DE16 IS NOT NULL THEN 1 ELSE 0 END cnt +
...
FROM TableA
jdlambert1Commented:
Cut & paste errror. Try this:

SELECT CASE WHEN ID IS NOT NULL THEN 1 ELSE 0 END +
 CASE WHEN DE6 IS NOT NULL THEN 1 ELSE 0 END +
 CASE WHEN DE16 IS NOT NULL THEN 1 ELSE 0 END +
...
 CASE WHEN DE136 IS NOT NULL THEN 1 ELSE 0 END as Cnt
FROM TableA
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

catchmeifuwantCommented:
select sum(decode(id,null,0,1)),sum(decode(de6,null,0,1)),sum(decode(de16,null,0,1))....
from mytable


pansophyAuthor Commented:
jdlambert1>  This works just fine, except I don't seem to be able to filter based on the value.  For instance, including only those rows with a count greater than 6.  When I enter >6 in the criteria, I get  "data type error in expression".

CASE WHEN ID >0 THEN 1 ELSE 0 END + CASE WHEN DE6 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE16 >0 THEN 1 ELSE 0 END + CASE WHEN DE26 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE36 >0 THEN 1 ELSE 0 END + CASE WHEN DE46 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE56 >0 THEN 1 ELSE 0 END + CASE WHEN DE66 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE76 >0 THEN 1 ELSE 0 END + CASE WHEN DE86 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE96 >0 THEN 1 ELSE 0 END + CASE WHEN DE106 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE116 >0 THEN 1 ELSE 0 END + CASE WHEN DE126 >0
                      THEN 1 ELSE 0 END + CASE WHEN DE136 >0 THEN 1 ELSE 0 END AS Cnt

jdlambert1Commented:
I think I understand what you mean. Try this:

SELECT Cnt FROM (
SELECT CASE WHEN ID IS NOT NULL THEN 1 ELSE 0 END +
 CASE WHEN DE6 IS NOT NULL THEN 1 ELSE 0 END +
 CASE WHEN DE16 IS NOT NULL THEN 1 ELSE 0 END +
...
 CASE WHEN DE136 IS NOT NULL THEN 1 ELSE 0 END as Cnt
FROM TableA
) sub
WHERE Cnt > 6
pansophyAuthor Commented:
Can I do that as a subquery??  

Here is what the full query looks like minus the "Where cnt > 6":

SELECT     ID, CASE WHEN de6 > 0 OR
                      de16 > 0 OR
                      de26 > 0 THEN 1 ELSE 0 END AS hr72,
                      CASE WHEN ID > 0 THEN 1 ELSE 0 END + CASE WHEN DE6 > 0 THEN 1 ELSE 0 END + CASE WHEN DE16 > 0 THEN 1 ELSE 0 END + CASE WHEN DE26
                       > 0 THEN 1 ELSE 0 END + CASE WHEN DE36 > 0 THEN 1 ELSE 0 END + CASE WHEN DE46 > 0 THEN 1 ELSE 0 END + CASE WHEN DE56 > 0 THEN 1 ELSE
                       0 END + CASE WHEN DE66 > 0 THEN 1 ELSE 0 END + CASE WHEN DE76 > 0 THEN 1 ELSE 0 END + CASE WHEN DE86 > 0 THEN 1 ELSE 0 END + CASE
                       WHEN DE96 > 0 THEN 1 ELSE 0 END + CASE WHEN DE106 > 0 THEN 1 ELSE 0 END + CASE WHEN DE116 > 0 THEN 1 ELSE 0 END + CASE WHEN DE126
                       > 0 THEN 1 ELSE 0 END + CASE WHEN DE136 > 0 THEN 1 ELSE 0 END AS cnt
FROM         dbo.tblTemp
WHERE     (CASE WHEN de6 > 0 OR
                      de16 > 0 OR
                      de26 > 0 THEN 1 ELSE 0 END = 1)
jdlambert1Commented:
Yes, it can be a subquery. I've mixed your code and mine and tweaked your WHERE clause a little bit:

SELECT * FROM (
 SELECT ID,
  CASE WHEN de6 > 0 OR de16 > 0 OR de26 > 0 THEN 1 ELSE 0 END AS hr72,
  CASE WHEN ID > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE6 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE16 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE26 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE36 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE46 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE56 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE66 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE76 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE86 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE96 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE106 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE116 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE126 > 0 THEN 1 ELSE 0 END +
  CASE WHEN DE136 > 0 THEN 1 ELSE 0 END AS cnt
 FROM dbo.tblTemp
 WHERE de6 > 0 OR de16 > 0 OR de26 > 0
) sub
WHERE Cnt > 6

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pansophyAuthor Commented:
Awesome as usual jdlambert1...thanks :)
jdlambert1Commented:
Glad I could help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.