0

I have a query in MySQL designed with multi JOINs. But the sum total value is different if I use a Group or just one total value.

If I run the query like below I get e.b. 3 lines with the correct calculated price (price*tax_amount).

But if I don't use Group because I want just the overall total the result is different. I guess because the query use the average tax amount instead of the correct amount for each item.

Do you have any idea how I can fix this?

Table:

  • contracts (contractID, some more data)
  • objects (objectID, contractID, price, taxID)
  • tax (taxID, tax_amount)

Query (works):

SELECT SUM(price*tax_amount) AS t_price 
FROM contracts 
JOIN objects 
  ON contracts.contractID = objects.contractID 
JOIN tax  
  ON tax.taxID = objects.taxID 
GROUP BY contracts.contractID

Result: 233.44 / 345.33 / 22.11

Query (Not work):

SELECT SUM(price*tax_amount) AS t_price 
FROM contracts 
JOIN objects 
 ON contracts.contractID = objects.contractID 
JOIN tax 
 ON tax.taxID = objects.taxID

Result: 527.33

Improve this question
7
  • 2
    please provide sample data and expected output Commented Feb 10, 2019 at 22:57
  • Aggregate functions (like sum, as you're using) usually need a group-by. The top query is taking each group of contractID, and summing the price * tax amount for each row in that group. In the second query, it's doing the same thing for ALL rows. That's why the difference in amounts, and a different number of return values. Commented Feb 10, 2019 at 23:16
  • 1
    @kermit, that doesn't make sense. What rows would be excluded in the first query and if so why would the total sum be larger in the first query if it contains fewer rows? Commented Feb 10, 2019 at 23:21
  • @JoakimDanielson I see what you're saying. 233.44 + 345.33 + 22.11 = 600.88. Why is the total summing to 527.33 in the 2nd. That's baffling. Sample data would be very useful. Commented Feb 10, 2019 at 23:36
  • 1
    @JoakimDanielson Think I got it - see the 2nd answer here: stackoverflow.com/questions/20074562/… - duplicate rows are being eliminated in the 2nd query, hence the difference in totals. Commented Feb 10, 2019 at 23:44
Related questions
0
Why do I get incorrect SUM in my MYSQL query joining two tables?
1
Incorrect SUM after Joining
5
MySQL: SUM() with JOIN returns incorrect values
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.