update query issue of GROUP BY sum() function in mysql

Question

Here is the sqlfiddle link
I am trying to use sum() function. It's working only SELECT query but it's give wrong value when I try this in subquery on UPDATE query.Here my SELECT query

SELECT 
  sum(i.pr_pur_cost_price*i.quantity) AS net_amount
FROM 
  product_purchases AS p
LEFT JOIN 
  product_purchase_item AS i
ON 
  p.product_purchase_item_id=i.product_purchase_item_id
WHERE 
  p.insert_operation=48
GROUP BY 
  p.insert_operation

Here My update query

UPDATE 
  shop_balance AS s 
SET 
   s.shop_balance=(s.shop_balance-(
      SELECT 
         sum(i.pr_pur_cost_price*i.quantity) as net_amount 
      FROM 
         product_purchases AS p
      LEFT JOIN 
         product_purchase_item AS i
      ON
         p.product_purchase_item_id=i.product_purchase_item_id
      WHERE 
         p.insert_operation=48
      GROUP BY 
         p.insert_operation
     )
   )
where s.shop_balance_id=n;

Why this wrong value in UPDATE query. is it problem for two columns in sum() function? or any best solution?

Could you perhaps show some sample data and desired (correct) result? — Joachim Isaksson, Aug 06 '13 at 17:00
my first SELECT query return a FLOAT datatype suppose 2200. if my s.shop_balance is 2500. it should return 300. but it gives wrong value — Imran, Aug 06 '13 at 17:08
`where s.shop_balance_id=n;` - Is "n" a valid shop_balance_id? — Declan_K, Aug 06 '13 at 17:08
@coder. I don't see how that error is happening. Your code looks to be working correctly. Can you add some data on sqlfiddle to show the problem? — Filipe Silva, Aug 06 '13 at 17:49
So in `WHERE` you're testing for equality `float` and `int`, right? — PM 77-1, Aug 06 '13 at 17:49
no `where s.shop_balance_id` is the primary id of shop balance. ok i'll show in sqlfiddle — Imran, Aug 06 '13 at 17:54
oh @FilipeSilva I'm so confusing. it's working on [sqlfiddle](http://sqlfiddle.com/#!2/1fd55/1). but it's not working on my localhost. why I dont know — Imran, Aug 06 '13 at 18:29
Your code looks to be good to work. You may have something strange on your existing script. Don't you have a trigger or something that might be updating the value on shop_balance? — Filipe Silva, Aug 06 '13 at 18:35
You may want to check the trigger then, it's a very reasonable explanation to your problem — Filipe Silva, Aug 06 '13 at 18:37
ok, thank you for your consult, I just already lost 3-4 hours. may be I'll find problem, note that may be I should use 'DOUBLE' for 'pr_pur_cost_price' because sqlfiddle alert me when i try up to thousands amount. I'll check now — Imran, Aug 06 '13 at 18:42
Yes. You can check http://stackoverflow.com/a/628639/1385896 for a good discussion on data types for currency. But that isn't the reason for your original problem. — Filipe Silva, Aug 06 '13 at 18:48
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/34939/discussion-between-coder-and-filipe-silva) — Imran, Aug 06 '13 at 20:00

score 1 · Accepted Answer · edited May 23 '17 at 10:31

1

Is this the same issue?

MYSQL Update using sum() result across multiple tables

The update statement does not allow group by, would it be a problem if you just removed it.

I am sorry that I am not able to see if it would be a problem in your scenario, but I am not able to understand the need for it in the context of your update statement.

Can you just try removing them or adding them in a way that it does not form part of the actual update statement itself?

edited May 23 '17 at 10:31

Community

1
1

answered Aug 06 '13 at 18:12

skv

1,793
3
19
27

[sqlfiddle](http://sqlfiddle.com/#!2/1fd55/1) working. but working on my localhost – Imran Aug 06 '13 at 18:35
This isn't the case. He is doing what worked for the poster in the mentioned issue. – Filipe Silva Aug 06 '13 at 18:39
Once there is a filter to select only a specific insert operation will the group by make a difference at all, and in sqlfiddle, just to ensure clarity you set it to Mysql? – skv Aug 06 '13 at 18:43
thank you very much @skv. and also sorry for let. I'm ensure that group by not working in `UPDATE` statement – Imran Aug 06 '13 at 19:58
oh I'm using trigger so don't worry, may be I am not miss behave on `UPDATE` statement – Imran Aug 06 '13 at 20:12

update query issue of GROUP BY sum() function in mysql

1 Answers1