SUM() twice in a Mysql query??????

Anon

Could any1 help me with this weird problem.

I am trying to make a query to compare to tables that are quite similar. This is for a shop. One table contains a ordered quantity, and the other table contains a delivered quantity. What i really want is to sum all the ordered quantities in table1 and compare it with sum delivered quantities in table2 for each order, and if sum table 1 is larger than sum table 2, print result. The trouble is that it seems like Mysql cant handle that i use sum for to tables in the same query. The sum results is completely wrong. However for test reasons i tried to sum only one of the tables, and then i get the rigt sum.

Hope this was understandable, and that one of you experts out there will help me with this problem.

Thanks alot.

Tom

toma42

So why not do two queries?

Are the two tables joined in your query? Are you specifying tablename.columnname in the sum?

Your best bet is to go with two queries and minimize headache.

timbach2

That's quite an aggravating problem... Even if you use DISTINCT orderID, MySQL seems to consider that the last thing it does, so you get multiple rows matching the other table. (And the sums can get rather large!) The solution is somewhat convoluted:

You'll have to use two queries. Depending on which version of MySQL you have, you can create a temp table on the fly and insert the values from table one into it (ie, INSERT INTO tmp SELECT orderID, SUM(qty) FROM table1 GROUP BY orderID) This gives you one row per order. Then, join that table with table2. This way you won't be getting multiple matches for each order.

If this didn't make sense, see the docs at http://www.mysql.com/doc/e/x/example-Maximum-column-group-row.html for further explanation.

good luck!

Tim