CODEWITHSUNDEEP
mysql
anthony
anthony

Reputation: 69

Update Table with sum based on column value

I would like to sum the values of a column, based on another columns value. My table looks like this:

|----------------------|-------------------|----------------|
|         col1         |       col2        |      result    |
|----------------------|-------------------|----------------|
|          a           |        5          |       12       |
|          a           |        7          |       12       |
|          b           |        9          |       209      |
|          b           |        200        |       209      |
|          c           |        56         |       56       |
|----------------------|-------------------|----------------|

The result column is the anticipated result. So the sum of the values in col2 for a in col1 would be 12 for every row that has col1=a. I want to do this with an update and set statement

Here's what I have so far:

    UPDATE Result o 
    INNER JOIN
    (
       SELECT col1, SUM(col2) sumcol
       FROM Result
       GROUP BY col1
    ) i ON o.col2 = i.col1
    SET o.result = i.sumcol

I'm having trouble finding the right way to do this. I'm mainly getting syntax errors. I've tried different variations of this query (e.g. here) but none has worked so far.

Thanks in advance!

Upvotes: 0

Views: 159

Answers (2)

Jocelyn
Jocelyn

Reputation: 11393

Try this modified query:

UPDATE Result o
INNER JOIN (
    SELECT col1, SUM(col2) AS sumcol
    FROM Result
    GROUP BY col1
) i ON o.col1 = i.col1
SET o.result = i.sumcol

Upvotes: 2

Gosfly
Gosfly

Reputation: 1300

Try the query below :

UPDATE Result o1 SET o1.result = (
     SELECT SUM(o2.col2) sumcol
     FROM Result o2
     GROUP BY o2.col1
     WHERE o2.col1 = o1.col1
)

EDIT : Jocelyn answer is the one, you probably joined on the wrong column and i didn't see it.

Upvotes: 1

Related Questions