CODEWITHSUNDEEP
mysqlsqlgroup-bysum
user3477172
user3477172

Reputation: 79

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

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?

Upvotes: 1

Views: 69

Answers (2)

juergen d
juergen d

Reputation: 204784

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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 ;

Upvotes: 1

Related Questions