CODEWITHSUNDEEP
c#sqlsql-server
user147373
user147373

Reputation:

sql query disregarding all rows that have all null columns

I have an SQL Server query that needs to count the number of rows returned, but I need to disregard rows where all the column values are NULL. Some rows have NULL values for some of the columns, but that is ok. I just need to filter out the ones that have ALL NULL values.

Right now I am returning all rows and using a SqlDataReader iterating the returned rows and counting the ones I need. I would like to move this into the query itself if possible.

Thanks!

EDIT: What I am trying to do is similar to this, but I am apparently having a hard time making the VS SQL editor recognize what I'm doing:

SELECT COUNT(sd.[ID]) 
FROM [Some Data] sd 
WHERE sd.[Data Name] = 'something' AND ((sd.q1 IS NOT NULL) OR (sd.q2 IS NOT NULL))

etc..

Upvotes: 2

Views: 306

Answers (3)

Hogan
Hogan

Reputation: 70523

You already selected an answer, but this is the most correct it should be the fastest because the SQL Engine can optimize it and "short circuit" it.

SELECT count(id)
FROM [Some Data]
WHERE NOT (COALESCE(Column1,Column2,Column3...) is null)

Upvotes: 1

Blorgbeard
Blorgbeard

Reputation: 103467

select count(id)
from [Some Data]
where not (Column1 is null and Column2 is null and Column3 is null ...)

Upvotes: 3

Randy Minder
Randy Minder

Reputation: 48412

How about something like this:

Select Count(*) From MyTable Where Column1 IS NOT NULL And Column2 IS NOT NULL And Column3 IS NOT NULL ...

Upvotes: 1

Related Questions