如何显示求和函数的结果集为零

Question

我正在使用函数sum将客户的所有发票相加:SUM(distinct(T1.[GTotal])) as InvoiceTotal

我知道我有一些客户有发票，但发票总额为0。函数将不显示这些记录。

我如何确保查询显示它们?

---

SELECT DISTINCT
    T2.CardCode as CustomerId, 
    T2.CntctPrsn as ContactPerson, 
    T2.Phone1 as Phone, 
    T4.GroupName as CustomerType, 
    T5.descript as Territory,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.Address ELSE NULL END) AS BillToCustomerName,  
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.Street ELSE NULL END) AS BillToAddress1,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.Block ELSE NULL END) AS BillToAddress2,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.City ELSE NULL END) AS BillToCity,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.State ELSE NULL END) AS BillToState,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.ZipCode ELSE NULL END) AS BillToZipCode,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.County ELSE NULL END) AS BillToCounty,
    MAX(CASE WHEN T3.AdresType = 'B' THEN T3.Country ELSE NULL END) AS BillToCountry,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.Address ELSE NULL END) AS ShipToCustomerName,  
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.Street ELSE NULL END) AS ShipToAddress1,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.Block ELSE NULL END) AS ShipToAddress2,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.City ELSE NULL END) AS ShipToCity,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.State ELSE NULL END) AS ShipToState,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.ZipCode ELSE NULL END) AS ShipToZipCode,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.County ELSE NULL END) AS ShipToCounty,
    MAX(CASE WHEN T3.AdresType = 'S' THEN T3.Country ELSE NULL END) AS ShipToCountry,
    COUNT(distinct(T0.[DocNum])) as Invoices,
    SUM(distinct(T1.[GTotal])) as InvoiceTotal 
FROM asap.dbo.OINV T0  
    INNER JOIN asap.dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry 
    INNER JOIN asap.dbo.OCRD T2 ON T0.CardCode = T2.CardCode 
    INNER JOIN asap.dbo.CRD1 T3 ON T2.CardCode = T3.CardCode
    INNER JOIN asap.dbo.OCRG T4 ON T2.GroupCode = T4.GroupCode 
    INNER JOIN asap.dbo.OTER T5 ON T2.Territory = T5.territryID 
WHERE T0.DocDate >= '2008-01-01 00:00:00.000' AND 
      T0.DocDate <= '2011-12-31 00:00:00.000' AND 
      T4.[GroupName] ='WholeSale' AND   
      T5.[descript] = 'Region 04' 
GROUP BY T2.[CardCode], 
      T2.[CntctPrsn], 
      T2.[Phone1], 
      T4.[GroupName], 
      T5.[descript]

我得到的所有发票都有一个总额，但是如果有发票没有任何$，他们不会显示在结果中。该报告将用于查看特定区域的现有客户群，并允许销售人员针对长期未购买或销售额低的客户进行定位。出于保密原因，以下是结果的精简版本:

Customer  Customer Type  Territory  Total Invoices  Total $ Spent
--------  -------------  ---------  --------------  -------------
C100177   Wholesale      Region 04  77              15813.1
C100208   Wholesale      Region 04  2               540
C100209   Wholesale      Region 04  5               809
C100213   Wholesale      Region 04  2               344.7
C100215   Wholesale      Region 04  5               1249.8

以下是缺失的记录:

Customer  Customer Type  Territory  Total Invoices  Total $ Spent
--------  -------------  ---------  --------------  -------------
C110885   Wholesale      Region 04  1               0
C123218   Wholesale      Region 04  1               0
C128694   Wholesale      Region 04  1               0
C158528   Wholesale      Region 04  1               0
C168646   Wholesale      Region 04  1               0

Answer 1

听起来有些客户的发票表中没有任何行?你应该使用外部连接。

SELECT
  c.CustomerId
 ,c.CustomerName
 ,SUM(COALESCE(i.GTotal, 0)) AS InvoiceTotal
FROM Customers c
  LEFT OUTER JOIN Invoices i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.CustomerName
ORDER BY c.CustomerName

Answer 2

先运行此查询

select count(*)
FROM asap.dbo.OINV T0       

然后分别添加下面的每个连接条件并再次运行查询

INNER JOIN asap.dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry      
INNER JOIN asap.dbo.OCRD T2 ON T0.CardCode = T2.CardCode      
INNER JOIN asap.dbo.CRD1 T3 ON T2.CardCode = T3.CardCode     
INNER JOIN asap.dbo.OCRG T4 ON T2.GroupCode = T4.GroupCode      
INNER JOIN asap.dbo.OTER T5 ON T2.Territory = T5.territryID  

其中一个连接条件将降低计数。例如，如果T0。对于没有金额的发票，doctry为空，那么第一个JOIN将降低计数…

一旦您知道是哪个表引起的问题，应该更容易修复

Answer 3

我同意Rob Boek的观点，您的解决方案应该至少在某个地方使用一个外部连接。在我看来，要进行外连接的表是OINV和OCRD，但是您还应该将WHERE中的一些条件移到相应的ON子句中，以使外连接发挥必要的作用。

更具体地说，我可能会像这样重新排列连接和条件:

…
FROM asap.dbo.OCRD T2  
    LEFT JOIN asap.dbo.OINV T0 ON T0.CardCode = T2.CardCode AND 
        T0.DocDate >= '2008-01-01 00:00:00.000' AND 
        T0.DocDate <= '2011-12-31 00:00:00.000' 
    LEFT JOIN asap.dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry 
    INNER JOIN asap.dbo.CRD1 T3 ON T2.CardCode = T3.CardCode
    INNER JOIN asap.dbo.OCRG T4 ON T2.GroupCode = T4.GroupCode 
    INNER JOIN asap.dbo.OTER T5 ON T2.Territory = T5.TerritoryID 
WHERE T4.[GroupName] ='WholeSale' AND   
      T5.[descript] = 'Region 04' 
…

我确实不同意Rob的一件事是如何应用COALESCE来消除SUM可能的NULL结果。在我看来，COALESCE应该应用于SUM的结果，而不是它的参数