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

Solution 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

Solution 2

You'll have to use ISNULL like this -

ISNULL(SUM(c.Logged), 0)      

Or, as Michael said, you can use a Left Outer Join.

Solution 3

I encountered this problem in Oracle. Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

NVL(SUM(c.Logged), 0)

Solution 4

The easiest, and most readable, way I've found to accomplish this is through:

CREATE PROC [dbo].[Incidents]
(@SiteName varchar(200))

AS

    SELECT SUM(COALESCE(i.Logged, 0)) AS LoggedIncidents
    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  

Solution 5

Just ran into this problem, Kirtan's solution worked for me well, but the syntax was a little off. I did like this:

ISNULL(SUM(c.Logged), 0)

Post helped me solve my problem though so thanks to all.

View more solutions

Comments

[Lluis Martinez]

If no rows are found in tbl_Sites SUM is not evaluated and returns NULL.

[TheCuBeMan]

I got confused among many anwsers I've found for the issue I was facing, but eventually "COALESCE(SUM(i.Logged),0)" did the trick, and in a very elegant and simple way. Thanks!!

[Gank]

but ISNULL not found in oracle

[khartvin]

@Gank in Oracle you can use NVL function for this purpose.