How to sum the values of column on duplicate key?

Question

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     |
+----+--------+-------+-------+

Michal Krasny · Answer 1 · 2015-11-27T10:54:00.683

3

You have to group by more columns.

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

edited Nov 27 '15 at 10:54

answered Nov 27 '15 at 10:47

Michal Krasny

5,434
7
36
64

Thanks. No actually I don't need to new ID, but I like to know how can I do that ... please add to your answer. thanks again. +1 – Shafizadeh Nov 27 '15 at 10:50
Sorry, I did not find any easy solution and I don't have much time to find it now – Michal Krasny Nov 27 '15 at 10:53

score 2 · Accepted Answer · answered Nov 27 '15 at 10:50

2

Group by name, key

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

answered Nov 27 '15 at 10:50

Abhishek Sharma

6,689
1
14
20

Thanks, +1. Can you please tell me how can I do that for inserting? I want to create a new row if there isn't identical rows *(according to `name` and `key`)*, Also if there is an identical row, then update it and sum their `value` numbers. – Shafizadeh Nov 27 '15 at 10:55
sorry can you explain more i not understand @Shafizadeh – Abhishek Sharma Nov 27 '15 at 11:08
insert these record on **mytable** @Shafizadeh – Abhishek Sharma Nov 27 '15 at 12:16
**insert into mytable (name,key,value) VALUES ('jack','1','5'),('bert','1','2')** try this query @Shafizadeh – Abhishek Sharma Nov 27 '15 at 12:29
This query ^ just inserts ...! I want if both `name` and `key` were equal, `value` get sum. – Shafizadeh Nov 27 '15 at 12:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/96354/discussion-between-rahautos-and-shafizadeh). – Abhishek Sharma Nov 27 '15 at 12:38

Smart003 · Answer 3 · 2015-12-08T05:19:27.700

0

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

edited Dec 08 '15 at 05:19

answered Nov 27 '15 at 10:48

Smart003

1,119
2
16
31

Well, your query is much complicated ..! Actually I can't understand it ..! Also as you see, I can do that using just one `group by` statement simply. anyway thanks for writing an answer under my question. – Shafizadeh Nov 27 '15 at 10:57

score 0 · Answer 4 · answered Sep 17 '20 at 20:41

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.

How to sum the values of column on duplicate key?

4 Answers4