CODEWITHSUNDEEP
t-sqlduplicatessumdistinct
topstuff
topstuff

Reputation: 129

Sum values double counted

I need help with a T-SQL query where I'm adding two columns together. The problem is my code does add them together but then double counts. I've searched around for similar queries but haven't found anything that relates well enough to this query. Here is my code:

SELECT DISTINCT t.Reference, 
u.FileID, 
u.UnpaidVAT, 
u.UnpaidFees, 
CAST(
SUM(u.UnpaidVAT+u.UnpaidFees) 
AS 
DECIMAL(8,2)) AS TotalOutstanding
FROM UnpaidFees u
LEFT JOIN Trans T ON u.Reference=t.InvoiceID
INNER JOIN Matters m ON u.FileId=m.FileId
WHERE t.Narration LIKE 'Invoice'
AND (u.UnpaidVAT>0 OR u.UnpaidFees>0)
AND m.Status=1
GROUP BY t.Reference, u.matterID, u.UnpaidVAT,u.UnpaidFees
;
Reference FileID UnpaidVAT UnpaidFees TotalOutstanding
1 20 20.00 100.00 240.00
2 21 50.00 250.00 600.00
3 21 12.00 60.00 144.00
4 22 20.00 100.00 240.00

Here you can see the results of my current query, Unpaid VAT and Unpaid Fees have been duplicate counted.

Also, t.InvoiceID, t.Narration, t.Reference and u.FileID will have duplicates entries in their respective tables.
Sample data from Trans table:

InvoiceID Narration Reference
1 Invoice INV1
1 Fee INV1
2 Invoice INV2
2 Fee INV2
3 Invoice INV3
3 Fee INV3
4 Invoice INV4
4 Fee INV4

Sample data from UnpaidFees table:
FileID Fees VAT Reference
20 100.00 20.00 1
21 250.00 50.00 2
22 20.00 100.00 3
22 10.00 50.00 4

Sample data from Matters table:
FileID Status
20 1
21 1
22 1
23 0

Where am I going wrong?

Upvotes: 0

Views: 78

Answers (1)

Jon Nigrine
Jon Nigrine

Reputation: 1

I'm sorry, I didn't have time to look closely, but I wouldn't think you need to sum columns that you are already grouping by. I suspect you have duplicate rows in one of your joins: u.Reference=t.InvoiceID or u.FileId=m.FileId. One of those is returning more than one record per join.

Maybe another column is needed to uniquely make one or both of the joins.

Upvotes: 0

Related Questions