CODEWITHSUNDEEP
mysqlsqlsumon-duplicate-key
Shafizadeh
Shafizadeh

Reputation: 10340

How to sum the values of column on duplicate key?

I have a table like this:

// mytable
+----+--------+-------+-------+
| id |  name  |  key  | value |
+----+--------+-------+-------+
| 1  | jack   | 1     | 10    |
| 2  | peter  | 1     | 5     |
| 3  | jack   | 2     | 5     |
| 4  | ali    | 1     | 2     |
| 5  | jack   | 1     | 5     |
| 6  | jack   | 1     | 10    |
| 7  | bert   | 4     | 2     |
| 8  | peter  | 2     | 10    |
| 9  | bert   | 4     | 5     |
+----+--------+-------+-------+

Now I want to sum the numbers of value where both name and key are identical. So, I want this output:

// mynewtable
+----+--------+-------+-------+
| id |  name  |  key  | value |
+----+--------+-------+-------+
| 1  | jack   | 1     | 25    |
| 2  | peter  | 1     | 5     |
| 3  | jack   | 2     | 5     |
| 4  | ali    | 1     | 2     |
| 7  | bert   | 4     | 7     |
| 8  | peter  | 2     | 10    |
+----+--------+-------+-------+

Is it possible to I do that?

Edit: How can I do that for insert?

// mytable
+----+--------+-------+-------+
| id |  name  |  key  | value |
+----+--------+-------+-------+
| 1  | jack   | 1     | 25    |
| 2  | peter  | 1     | 5     |
| 3  | jack   | 2     | 5     |
| 4  | ali    | 1     | 2     |
| 7  | bert   | 4     | 7     |
| 8  | peter  | 2     | 10    |
+----+--------+-------+-------+

Inserting these rows:

    +----+--------+-------+-------+ 
    | 10 | jack   | 1     | 5     |
    +----+--------+-------+-------+ 
    +----+--------+-------+-------+ 
    | 11 | bert   | 1     | 2     |
    +----+--------+-------+-------+

What I want: (output)

// mynewtable
+----+--------+-------+-------+
| id |  name  |  key  | value |
+----+--------+-------+-------+
| 1  | jack   | 1     | 30    |
| 2  | peter  | 1     | 5     |
| 3  | jack   | 2     | 5     |
| 4  | ali    | 1     | 2     |
| 7  | bert   | 4     | 7     |
| 8  | peter  | 2     | 10    |
| 11 | bert   | 1     | 2     |
+----+--------+-------+-------+

Upvotes: 0

Views: 3441

Answers (4)

Abecee
Abecee

Reputation: 2393

For the first part (to get the id column in the way requested), you could work along:

INSERT INTO mynewtable
    (id, name, `key`, `value`)
SELECT
  MIN(id), name, `key`, SUM(`value`)
FROM mytable
GROUP BY name, `key`
;

Now, provided mynewtable is defined with a unique index on name and key like

CREATE TABLE mynewtable
(id INT, name VARCHAR(5), `key` INT, `value` INT, UNIQUE (name, `key`));

you'd get the requested result with

INSERT INTO mynewtable
    (id, name, `key`, `value`)
VALUES
    (10, 'jack', 1, 5),
    (11, 'bert', 1, 2)
ON DUPLICATE KEY UPDATE `value` = `value` + VALUES(`value`)
;

Beware:

  • It requires the unique index on name and key to work.
  • It might not work correctly, if there are other unique indexes and/or a primary key on the same table as well.

NB: Please try to avoid the use of reserved words such as value and key for, e.g., column names.

Upvotes: 0

Smart003
Smart003

Reputation: 1119

check this

CREATE TABLE #testing_123
    ([id] int, [name] varchar(5), [key] int, [value] int)
;

INSERT INTO #testing_123
    ([id], [name], [key], [value])
VALUES
    (1, 'jack', 1, 10),
    (2, 'peter', 1, 5),
    (3, 'jack', 2, 5),
    (4, 'ali', 1, 2),
    (5, 'jack', 1, 5),
    (6, 'jack', 1, 10),
    (7, 'bert', 4, 2),
    (8, 'peter', 2, 10),
    (9, 'bert', 4, 5)
;

query used was 

select min(id) id ,name,[key],sum(value) value from #testing_123 group by name,[key] order by 1

output after insert

enter image description here

Upvotes: 0

Michal Krasny
Michal Krasny

Reputation: 5916

You have to group by more columns.

select name, key, sum(value) from mytable group by name, key;

Upvotes: 3

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Group by name, key

 select name, key, sum(value) as value 
 from mytable group by name,key

Upvotes: 2

Related Questions