How to show result sets that are zero from the sum function

Question

I am using the function sum to add up all of the invoices for a customer: SUM(distinct(T1.[GTotal])) as InvoiceTotal

I know that I have some customers that have an invoice but the invoice total is 0. The function will not show these records.

How can I make sure the query displays them?

---

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]

I am getting all invoices that have a total, however if there are invoices that don't have any $ they are not showing in the results. The report will be used to look at an existing customer base for a specific territory and allow the sales people to target the customers who haven't purchased in a long time or have low sales. For confidentiality reasons here is a condensed version of the results:

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

Here are records that are missing:

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

I agree with Rob Boek on that your solution should use at least one outer join somewhere. In my view, the tables to be outer joined are OINV and OCRD, but you should also move some conditions from WHERE to the corresponding ON clause, for the outer joins to take the necessary effect.

To be more specific, I would probably re-arrange the joins and conditions like this:

…
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' 
…

One thing on which I do not agree with Rob is how to apply COALESCE to eliminate a possible NULL result of the SUM. In my opinion, COALESCE should be applied to the result of the SUM, not to its argument.

Answer 2

Run this query first

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

Then add each join condition below separately and run the query again

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  

One of the join conditions will lower the count. For example, if T0.DocEntry is empty for the invoices without amounts, then the first JOIN will lower your count...

Once you know which table is causing the problem, it should be easier to fix

Answer 3

It sounds like some customers don't have any rows in the Invoices table? You should use an outer join.

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