Why does SUM from one table rises if I do an INNER JOIN with another table?

Question

Why does SUM from one table rises if I do an INNER JOIN with another table?

jankok

SSC Enthusiast

Points: 172
More actions
January 30, 2021 at 10:15 am

#3839022
I can't figure this out... If I write this query...
```
SELECT SUM(d.Quantity)
FROM Invoice i
INNER JOIN InvDet d ON i.InvoiceId = d.InvoiceId
```
I get the SUM that I want, but if I do an inner join with another table I get much larger sum.
```
SELECT SUM(d.Quantity)
FROM Invoice i
INNER JOIN InvDet d ON i.InvoiceId = d.InvoiceId
INNER JOIN WorkOrder wo ON d.InvDetId = wo.InvDetId
```
I was expecting for quantity sum to remain the same, how can it be that when I make another join that the sum gets bigger? More importantly, what can I do to get desired results?
February 1, 2021 at 7:43 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

I'm thinking the answer is pretty simple... you probably have more than one WorkOrder per invoice and that made a one-to-many join, which creates more rows (essentially, a duplication of rows, also known as "Relational Multiplication") behind the scenes. You'd have to look at the actual execution plan of both queries to make that determination.

For the given queries, I see no reason in particular to join to the WorkOrder table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

A common approach to fix that issue in general is to isolate the SUM() in a separate query, so that the later join doesn't increase the total. For example, something like this:

SELECT wo.<whatever>, id.*
FROM (
    SELECT d.InvDetId, SUM(d.Quantity) AS Quantity
    FROM Invoice i
    INNER JOIN InvDet d ON i.InvoiceId = d.InvoiceId
    GROUP BY d.InvDetId
) AS id 
INNER JOIN WorkOrder wo ON id.InvDetId = wo.InvDetId

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".