Hi,

I have a query in sql server that when it returns no results its just an empty screen. However, I would like no results to return zero. I've tried ISNULL and Coalesce, but they are not working because I am using them with an aggregate, i.e. ISNULL(Count(Distinct name,0)). Is there a way that I can have the results retun a zero if there are no results and if so how. Any and all help would be appreciated

You'll have to check if there are any results first. Something like this:

IF EXISTS(Select YourValue From YourTable)
Select YourValue From YourTable
ELSE
SELECT 0

You can also nest the query ie:


SELECT isnull(yourvalue,0) AS name
FROM
(
SELECT yourvalue
FROM yourtable
WHERE somecriteria
)

I don't believe that would work if there were no results return from the nested select. It won't return a null value ... just no rows at all.

If there's no row[s] returned then no, it won't work. I've assumed that the nested query could return NULL as a value based on the OP testing the calculated column for NULL and me not reading the post properly.

If the potential is for 0 rows to be returned then your example is the way to go.