Issue with join and sum in mysql query: invalid use of group function

Question

I have this update query:

UPDATE
  account INNER JOIN c1
  ON c1.user = account.u_id
SET
  account.amount = account.amount + sum(c1.total_1)
WHERE
  t_ype =8

but it returns Invalid Use Of Group Function... where is my mistake?

Gordon Linoff · Accepted Answer · 2014-10-08 16:49:46Z

1

You need to do the aggregation before the join:

UPDATE account INNER JOIN
       (SELECT c1.user, sum(c1.total_1) as sumc
        FROM c1
        GROUP BY c1.user
       ) c1
       ON c1.user = account.u_id
    SET account.amount = account.amount + sumc
    WHERE account.t_ype = 8 ;

answered Oct 8, 2014 at 16:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2014-10-08 16:50:04Z

1

UPDATE account 
JOIN 
(
  select user, sum(total_1) as sum_total
  from c1
  group by user
) c1 ON c1.user = account.u_id 
SET account.amount = account.amount + sum_total
WHERE account.t_ype = 8

answered Oct 8, 2014 at 16:50

juergen d

205k40 gold badges304 silver badges377 bronze badges

Collectives™ on Stack Overflow

Issue with join and sum in mysql query: invalid use of group function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related