Sum column and remove lines sum

Question

I want to sum the "amount" column grouped by name and server, and remove duplicate lines. I want those changes are saved (not only with one query).

For example, here is begin data:

Id	server	name	amount
1	20	a	1
2	20	a	1
3	30	b	2
4	20	b	1
5	20	a	1

And here is what I want:

Id	server	name	amount
1	20	a	3
3	30	b	2
4	20	b	1

Actually, I can do it with:

SELECT SUM(amount) as amount FROM myTable GROUP BY server, name;

But, I will only get the value for the amount column. It will not update the table and not remove other lines.

If I do something like UPDATE myTable SET amount = (SELECT SUM(amount) as amount FROM myTable WHERE server = myTable.server AND name = myTable.name) it will not works as I can't do a select into an update.

So, how can I do?

Answer 1

Basically you are asking to delete the records from your table I hope this is a single process.

This can be done by inserting your "final" data into a temporary table truncate your existing table and then insert the data from the temporary table to your main table.

As per the id it seems excess to me, you can remove it and leave it autoincrement.

---

Code

SET autocommit=0;
LOCK TABLES myTable WRITE;

create temporary table temp_myTable like myTable;
insert into temp_myTable SELECT min(id) as id,server,name,SUM(amount) as amount 
FROM myTable 
GROUP BY server, name;
truncate myTable;
insert into myTable select * from temp_myTable;
drop temporary table temp_myTable;

COMMIT;
UNLOCK TABLES;

Result

select * from myTable;

id  server  name    amount
1      20    a       3
3      30    b       2
4      20    b       1

See example

See example with new id-s