更改SUM会将NULL返回零

Question

我有一个存储过程，如下所示：

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.

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

我曾尝试使用合并和isull无济于事。

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

有没有一种方法可以正确格式化此格式？

干杯，

背风处

Answer 1

放在外面：

SELECT COALESCE(

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

如果要返回多行，请将INNER JOIN更改为 LEFT JOIN

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

顺便说一句，如果没有保证，请勿在聚合函数中放置任何函数或表达式，例如，不要在ISUM中放置ISNULL，COALESCE，在聚合内部使用函数/表达式削弱性能，查询将通过表扫描执行