Changing no results to zero

[nh1234]

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

 

[pdx_man]

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

 

[tehNellie]

You can also nest the query ie:

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

 

[pdx_man]

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.

 

[tehNellie]

If there's no row 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.