I have a scenario where I'm trying not to count a row where it has a zero, blank or NULL. But I'm not sure how to. I have used ISNULL to replace it with blank but the result shows zero. I don't want zero because it messes up my averages etc. The screen shot below should show 17 in the bottom as total but it's showing 18 because it's counting the null as row count. This null row has an N/A as a value in the data set but my count counts it as a row. I'm using count() because I have many other columns so I can not change the count(). Any ideas on how to show the total as 17 instead of 18?

Thank you

CodePudding user response：

Some SQL implementations (I think this is also proper ANSI standard but don't know that for sure) exhibit a different behaviour for COUNT(*) VS COUNT(field).

The former will include NULLs, the latter will exclude them.

CodePudding user response：

you can use SUM like this

SELECT  SUM(CASE WHEN ISNULL(testeq4,'') <> '' THEN 1 ELSE 0 end)
FROM YourTable

CodePudding user response：

Try this:

SELECT SUM(CASE WHEN ISNULL(TestQ4,0)=0 THEN 0 ELSE 1 END)