LEFT JOIN returns duplicate SUM values

Question

I have the following tables:

create table Invoices
(
  InvoiceID int,
  InvoiceNumber int,
  InvoiceDate date,
  SupplierName varchar(250),
  SupplierCode varchar(20),
  InvoiceValue decimal(18,2)  
);

insert into Invoices (InvoiceID, InvoiceNumber, InvoiceDate, SupplierName, SupplierCode, InvoiceValue) values 
(1,700,'2021-01-01','ACME','A01',978.32),
(2,701,'2021-01-02','MACROD','A02',772.81),
(3,702,'2021-01-03','CODECO','A03',938.20),
(4,703,'2021-01-04','ACME','A03',892.18),
(5,704,'2021-01-05','CODECO','A03',791.41),
(6,705,'2021-01-06','DRONIX','A04',469.03);

create table Payments
(
  InvoiceID int,
  PaymentDate date,  
  PaymentValue decimal(18,2)   
);

insert into Payments (InvoiceID, PaymentDate, PaymentValue) values 
(1, '2021-01-11', 500.00),
(1, '2021-01-12', 50.00),
(1, '2021-02-13', 100.00),
(3, '2021-02-14', 10.00),
(4, '2021-03-15', 200.00),
(3, '2021-03-16', 300.00),
(5, '2021-04-17', 75.00),
(1, '2021-04-18', 30.00);

This is the query I'm using:

SELECT
      a.SupplierName, 
      a.SupplierCode, 
      SUM(a.TotalInvoiceValue), 
      ISNULL(SUM(b.PaidAmount), 0), 
      SUM(a.TotalInvoiceValue) - ISNULL(SUM(b.PaidAmount), 0)
FROM (
    SELECT
      InvoiceID,
      SupplierName,
      SupplierCode,
      SUM(InvoiceValue) AS TotalInvoiceValue
    FROM Invoices  
    WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
    GROUP BY
      InvoiceID,
      SupplierName,
      SupplierCode
) a 
LEFT JOIN (
    SELECT
      InvoiceID,
      ISNULL(SUM(PaymentValue),0) AS PaidAmount
    FROM Payments
    GROUP BY InvoiceID
) b 
    ON a.InvoiceID=b.InvoiceID 
GROUP BY
  a.InvoiceID,
  a.SupplierName,
  a.SupplierCode
ORDER BY
  a.SupplierName

The query above is returning multiple rows for the same SupplierName from Payments table.

I'm using Microsoft SQL Server 2005.

See the SQL fiddle here

Reminder: SQL Server 2005 has been completely unsupported for about 5 years now. It has known security issues and simply doesn't support a lot of syntax taken for granted on more recent versions. You should really be looking at upgrade paths ASAP. — Thom A
– Thom A ♦, Commented Jun 29, 2021 at 19:29
In fact, are you sure you're using 2005? The VALUES table construct was introduced in SQL Server 2008. — Thom A
– Thom A ♦, Commented Jun 29, 2021 at 19:32
Often the inclusion of columns in a GROUP BY clause that do not appear in the SELECT list are a clue that your desired goal doesn't quite match your query. So what does your resultset represent? Grouping by InvoiceID means you are requesting information at the invoice level but your select list implies you want values at a supplier level. And you have the added issue of the same supplier having multiple codes. Clarify that goal. What should your output be based on the sample data? — SMor
– SMor, Commented Jun 29, 2021 at 21:14
@SMor, the end result should be a list of every supplier with their invoiced value and received payments, just like the query result I'm getting now, but grouped by supplier. — milo2011
– milo2011, Commented Jun 29, 2021 at 22:24
Whitespace doesn't cost, you know. If you were on a later version you could remove that left join and replace it with a window aggregate on the first table ISNULL(SUM(SUM(b.PaidAmount)) OVER (PARTITION BY InvoiceID), 0) — Charlieface
– Charlieface, Commented Jun 29, 2021 at 23:54

Andrew O'Brien · Accepted Answer · 2021-06-29 19:40:27Z

Because you are grouping by (a.InvoiceID, a.SupplierName, a.SupplierCode), you will have a distinct record for each combination of invoice ID, supplier name, and supplier code. Try grouping by the non aggregate fields in your select statement to return distinct summary rows (in this case, do not group by invoice ID as it is not in your select statement).

In this case, you select SupplierName and SupplierCode. This will duplicate the SupplierName for every Supplier Code. If you want a distinct summary record for SupplierName, do not select Supplier Code.

With SupplierCode

SELECT a.SupplierName, 
  a.SupplierCode, 
  SUM(a.TotalInvoiceValue), 
  ISNULL(SUM(b.PaidAmount),0), 
  SUM(a.TotalInvoiceValue)-ISNULL(SUM(b.PaidAmount),0)
FROM 
  (SELECT InvoiceID, SupplierName, SupplierCode, SUM(InvoiceValue) AS TotalInvoiceValue FROM Invoices  
  WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29' 
  GROUP BY InvoiceID, SupplierName, SupplierCode) a 
LEFT JOIN 
  (SELECT InvoiceID, ISNULL(SUM(PaymentValue),0) AS PaidAmount FROM Payments GROUP BY InvoiceID) b 
ON a.InvoiceID=b.InvoiceID 
GROUP BY a.SupplierName, a.SupplierCode
ORDER BY a.SupplierName

Without SupplierCode

SELECT a.SupplierName, 
  SUM(a.TotalInvoiceValue), 
  ISNULL(SUM(b.PaidAmount),0), 
  SUM(a.TotalInvoiceValue)-ISNULL(SUM(b.PaidAmount),0)
FROM 
  (SELECT InvoiceID, SupplierName, SupplierCode, SUM(InvoiceValue) AS TotalInvoiceValue FROM Invoices  
  WHERE InvoiceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29' 
  GROUP BY InvoiceID, SupplierName, SupplierCode) a 
LEFT JOIN 
  (SELECT InvoiceID, ISNULL(SUM(PaymentValue),0) AS PaidAmount FROM Payments GROUP BY InvoiceID) b 
ON a.InvoiceID=b.InvoiceID 
GROUP BY a.SupplierName
ORDER BY a.SupplierName

Thanks, Andrew! The removal of 'a.InvoiceID' was just what I needed for the query to return the desired result (grouping by supplier name and code only)

Collectives™ on Stack Overflow

LEFT JOIN returns duplicate SUM values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related