SQL 更改 SUM 返回 NULL 为零

Changing a SUM returned NULL to zero

提问by Icementhols

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.

我遇到的问题是某个站点本月没有任何事件，因此当我运行此 proc 时，我为该站点返回了一个 NULL 值，但我需要返回一个零/0 才能在其中使用SSRS 中的图表。

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

我试过使用 coalesce 和 isnull 无济于事。

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

Is there a way to get this formatted correctly?

有没有办法让这个格式正确？

Cheers,

干杯，

Lee

李

回答by Michael Buen

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

如果要返回多行，请将 INNER JOIN 更改为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

顺便说一句，如果没有保证，不要将任何函数或表达式放在聚合函数中，例如不要将 ISNULL、COALESCE 放在 SUM 中，在聚合中使用函数/表达式会降低性能，查询将使用表扫描执行

回答by Kirtan

You'll have to use ISNULLlike this -

你必须ISNULL像这样使用-

ISNULL(SUM(c.Logged), 0)      

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

或者，正如迈克尔所说，您可以使用左外连接。

回答by Guangtong Shen

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:

我在 Oracle 中遇到了这个问题。Oracle 没有ISNULL()函数。但是，我们可以使用该NVL()函数来实现相同的结果：

NVL(SUM(c.Logged), 0)

回答by Jeremiah Peschka

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  

回答by jaywon

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

刚遇到这个问题，Kirtan 的解决方案对我很有效，但语法有点不对。我是这样的：

ISNULL(SUM(c.Logged), 0)

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

帖子帮助我解决了我的问题，但感谢所有人。

回答by Rob Stevenson-Leggett

You could wrap the SELECT in another SELECT like so:

您可以将 SELECT 包装在另一个 SELECT 中，如下所示：

 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
)