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

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
couganet
  • 13
  • 3
  • 2
    please provide sample data and expected output – Derviş Kayımbaşıoğlu Feb 10 '19 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. – kermit Feb 10 '19 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? – Joakim Danielson Feb 10 '19 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. – kermit Feb 10 '19 at 23:36
  • 1
    @JoakimDanielson Think I got it - see the 2nd answer here: https://stackoverflow.com/questions/20074562/group-by-without-aggregate-function - duplicate rows are being eliminated in the 2nd query, hence the difference in totals. – kermit Feb 10 '19 at 23:44
  • @kermit interesting link with some good answers but I still can’t understand why the total sum of the first query is larger, could it have something to do with the use of join? Could it be that for instance the join to the `tax` table should be a `LEFT JOIN`? – Joakim Danielson Feb 11 '19 at 06:27
  • @JoakimDanielson, the only thing I can think of is that because the aggregate function without the group-by clause does an implicit "select distinct", it is excluding rows that are picked up when a group-by is added, and there is then no implicit "select distinct." – kermit Feb 12 '19 at 00:58

0 Answers0