Count Non-null data

Dear Experts,

If I have table with 4-column (Col1, Col2, Col3 & Col4),
considering Col1 as pK, how can I efficiently count other columns with non-null or greater than zero (0) value.
The expected answers is between 0 and 3 and could another view column.


Thanks.
JimiJ13I T ConsultantAsked:
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.

Ramachandar NCommented:
SELECT COUNT(1) AS 'NullCount' FROM mytable WHERE ISNULL(a,'') = '' --a here is the column name
UNION
SELECT COUNT(1) AS 'NonNullCount' FROM mytable WHERE ISNULL(b,'') <> '' --b here is the column name
UNION
SELECT COUNT(1) AS 'NullCountInt' FROM mytable WHERE ISNULL(c,0) = 0' --c here is the numeric column name
JimiJ13I T ConsultantAuthor Commented:
Thanks Ramachandar,

That solution might indeed work but it will be very costly.  I'm expecting a simpler and efficient one.
Ramachandar NCommented:
Sure. Can you tell me the columns' types other than primary key?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JimiJ13I T ConsultantAuthor Commented:
All column types are decimal.
Ramachandar NCommented:
See if this is the one you expected.
SELECT SUM(CASE
		      WHEN ISNULL(charcol1,'') <> ''
                                         THEN 1
		      ELSE 0
		END) AS 'CharNonNullCount'
	   SUM(CASE
		      WHEN ISNULL(deccol1,0) > 0
                                          THEN 1
		      ELSE 0
                           END) AS 'DecNonNullCount'
FROM
   mytable

Open in new window

JimiJ13I T ConsultantAuthor Commented:
BTW:  If columns (2, 3 & 4) are all null or Zero, the answer will be Zero (0). If all columns have greater than Zero (0) values, the count will be 3.

Thanks.
Ramachandar NCommented:
I get it. So there is only one row in the table. Right?
JimiJ13I T ConsultantAuthor Commented:
We don't need summation.  We only want to add columns (Col 1 + Col 2 + Col 3) with more than Zero (0) value as 1. So the answer could be 0 or 3.  

I hope that makes sense.

Thanks.
Ramachandar NCommented:
I guess, you want to count the NonNull-and-greater-than-zero count for all the rows in the table and need a result like the below.
Col1 Col2 Col3 Col4
10   4    0    NULL
11   0    1    2
12   5    2    9
13   0   NULL  NULL

Expected Result:
Col1   NonNullCount
10     1
11     2
12     3
13     0

If i understand correctly, use this:
SELECT Col1, (CASE
                WHEN ISNULL(Col2,0) > 0
                   THEN 1
                ELSE 0
             END +
             CASE
                WHEN ISNULL(Col3,0) > 0
                   THEN 1
                ELSE 0
             END +
             CASE
                WHEN ISNULL(Col3,4) > 0
                   THEN 1
                ELSE 0
             END) as "NonNullCount"
FROM
    mytable

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
JimiJ13I T ConsultantAuthor Commented:
There are many rows but I wanted only to count per row needed for the business logic.
JimiJ13I T ConsultantAuthor Commented:
This line will always result to 1:

  CASE
                WHEN ISNULL(Col3,4) > 0
                   THEN 1
Ramachandar NCommented:
Sorry that's a typo. You can change that 4 to 0.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to have something like this:
SELECT CASE
			WHEN SUM(CASE 
						WHEN col2 > 0 THEN 1
						ELSE 0
					END) > 0 THEN 1
			ELSE 0
		END + 
		CASE
			WHEN SUM(CASE 
						WHEN col3 > 0 THEN 1
						ELSE 0
					END) > 0 THEN 1
			ELSE 0
		END + 
		CASE
			WHEN SUM(CASE 
						WHEN col4 > 0 THEN 1
						ELSE 0
					END) > 0 THEN 1
			ELSE 0
		END AS TotalCount
FROM TableName

Open in new window

JimiJ13I T ConsultantAuthor Commented:
Thanks for the help. I have actually figured out the solution just after my posting.
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
Microsoft SQL Server

From novice to tech pro — start learning today.