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

Michael Buen · Accepted Answer · 2010-05-10T13:28:54.857

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

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!! — TheCuBeMan, Jan 12 '16 at 07:41

score 39 · Answer 2 · edited May 10 '13 at 22:54

39

You'll have to use ISNULL like this -

ISNULL(SUM(c.Logged), 0)

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

edited May 10 '13 at 22:54

unkulunkulu

11,576
2
31
49

answered May 10 '10 at 12:58

Kirtan

21,295
6
46
61

`ifnull` in sqlite: https://www.sqlite.org/lang_corefunc.html#ifnull – Yann C. Nov 17 '22 at 17:39

score 9 · Answer 3 · edited Mar 27 '19 at 14:26

9

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)

edited Mar 27 '19 at 14:26

d219

2,707
5
31
36

answered Oct 01 '16 at 00:50

Guangtong Shen

1,402
1
11
12

score 3 · Answer 4 · answered May 10 '10 at 13:10

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

If no rows are found in tbl_Sites SUM is not evaluated and returns NULL. — Lluis Martinez, Nov 30 '12 at 15:34

score 1 · Answer 5 · answered May 10 '10 at 12:59

You could wrap the SELECT in another SELECT like so:

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

AS

SELECT COALESCE(TotalIncidents  ,0)
FROM (
  SELECT
  (  
    SELECT SUM(i.Logged) as TotalIncidents  
    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
)

score 1 · Answer 6 · answered Aug 12 '10 at 20:04

1

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.

answered Aug 12 '10 at 20:04

jaywon

8,164
10
39
47

but ISNULL not found in oracle – Gank May 18 '16 at 01:43
@Gank in Oracle you can use NVL function for this purpose. – khartvin Aug 13 '19 at 10:27

score 0 · Answer 7 · answered Sep 10 '22 at 18:03

0

The code you've posted above

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

would not work due to wrong order

what would work is the following

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

It evaulates the expression SUM then if it returns NULL it is replaced with 0.

answered Sep 10 '22 at 18:03

peskandritsa2

1

Changing a SUM returned NULL to zero

7 Answers7

Linked