issue with mysql sum used along with inner join

the sumed field value triples!

[naiquevin]

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]

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

[LifeinAsia]

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