asked on

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.

Ramachandar N

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

JimiJ13

ASKER

Thanks Ramachandar,

That solution might indeed work but it will be very costly. I'm expecting a simpler and efficient one.

Ramachandar N

Sure. Can you tell me the columns' types other than primary key?

JimiJ13

ASKER

All column types are decimal.

Ramachandar N

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

JimiJ13

ASKER

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 N

I get it. So there is only one row in the table. Right?

JimiJ13

ASKER

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.

ASKER CERTIFIED SOLUTION

Ramachandar N

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JimiJ13

ASKER

There are many rows but I wanted only to count per row needed for the business logic.

JimiJ13

ASKER

This line will always result to 1:

CASE
WHEN ISNULL(Col3,4) > 0
THEN 1

Ramachandar N

Sorry that's a typo. You can change that 4 to 0.

Vitor Montalvão

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

JimiJ13

ASKER

Thanks for the help. I have actually figured out the solution just after my posting.