Changing a SUM returned NULL to zero

Question

I have a stored procedure as follows:

CREATE PROC [dbo].[Incidents] (@SiteName varchar(200)) AS SELECT (       SELECT SUM(i.Logged)       FROM tbl_Sites s       INNER JOIN tbl_Incidents i       ON s.Location = i.Location       WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)       GROUP BY s.Sites   )  AS LoggedIncidents  'tbl_Sites contains a list of reported on sites. 'tbl_Incidents contains a generated list of total incidents by site/date (monthly) 'If a site doesn't have any incidents that month it wont be listed. 

The problem I'm having is that a site doesn't have any Incidents this month and as such i got a NULL value returned for that site when i run this proc, but i need to have a zero/0 returned to be used within a chart in SSRS.

I've tried using coalesce and isnull to no avail.

    SELECT COALESCE(SUM(c.Logged,0))     SELECT SUM(ISNULL(c.Logged,0)) 

Is there a way to get this formatted correctly?

Cheers,

Lee

Answer 1

Put it outside:

SELECT COALESCE(  (       SELECT SUM(i.Logged)       FROM tbl_Sites s       INNER JOIN tbl_Incidents i       ON s.Location = i.Location       WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)       GROUP BY s.Sites   ), 0)  AS LoggedIncidents 

If you are returning multiple rows, change INNER JOIN to LEFT JOIN

SELECT COALESCE(SUM(i.Logged),0) FROM tbl_Sites s   LEFT JOIN tbl_Incidents i   ON s.Location = i.Location   WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)   GROUP BY s.Sites   

By the way, don't put any function or expression inside aggregate functions if it's not warranted, e.g. don't put ISNULL, COALESCE inside of SUM, using function/expression inside aggregation cripples performance, the query will be executed with table scan