issue with mysql sum used along with inner join

Forum Moderators: open

Message Too Old, No Replies

issue with mysql sum used along with inner join

the sumed field value triples!

naiquevin

3:07 pm on Nov 21, 2009 (gmt 0)

I am trying to get sum of all orders from a table grouping by the type of currency and using inner join along with sum to do the same ..

the query is as follows

SELECT sum(xo.OrderTotal) AS total, xo.order_currency_code, xc.value FROM orders xo INNER JOIN currency xc ON xo.order_currency_code = xc.code WHERE xo.id = 3 GROUP BY xo.order_currency_code

but it results in the total value being triple of what it should be!

If join is removed, it gives the expected result..
kindly help

whoisgregg

2:56 pm on Nov 25, 2009 (gmt 0)

If removing the join gives the expected result, then I'd recommend removing the join. :)

LifeinAsia

5:09 pm on Nov 25, 2009 (gmt 0)

The problem may be that you're not doing a GROUP by all the selected fields. Try:
SELECT sum(xo.OrderTotal) AS total, xo.order_currency_code, xc.value
FROM orders xo INNER JOIN currency xc ON xo.order_currency_code = xc.code
WHERE xo.id = 3
GROUP BY xo.order_currency_code, xc.value