Geek Answers Handbook

MySQL How can I add column values ​​together and remove duplicate rows?

Good afternoon,

I have a MySQL table in which there are several duplicate rows that need to be removed by adding the value from one column in duplicated rows to the original.

The problem arose when the other column had the wrong values, and now it is fixed, but it left the scales divided between different lines that need to be added together. Then new lines that have been added should be deleted.

In this example, the userid column determines whether they are duplicate (or triple). userid 6 is duplicated, and user ID 3 is three times.

As an example for userid 3, he should add all the residues from rows 3, 11 and 13 and should put this number on row 3 and then delete rows 11 and 13. The balance columns of both of them should be added together to the original, bottom row of the identifier and new, higher identifier lines should be removed.

ID | balance | userid --------------------- 1 | 10 | 1 2 | 15 | 2 3 | 300 | 3 4 | 80 | 4 5 | 0 | 5 6 | 65 | 6 7 | 178 | 7 8 | 201 | 8 9 | 92 | 9 10 | 0 | 10 11 | 140 | 3 12 | 46 | 6 13 | 30 | 3

I hope this is clear enough and that I have provided enough information. Thanks =)

+4
source share
4 answers

Two steps.

1. Update:

  UPDATE tableX AS t JOIN ( SELECT userid , MIN(id) AS min_id , SUM(balance) AS sum_balance FROM tableX GROUP BY userid ) AS c ON t.userid = c.userid SET t.balance = CASE WHEN t.id = c.min_id THEN c.sum_balance ELSE 0 END ;

2. Remove the extra lines:

  DELETE t FROM tableX AS t JOIN ( SELECT userid , MIN(id) AS min_id FROM tableX GROUP BY userid ) AS c ON t.userid = c.userid AND t.id > c.min_id WHERE t.balance = 0 ;

Once you decide this, it would be nice to add a UNIQUE restriction on userid , since it seems you want to keep balance for each user here. This will avoid duplicates in the future. You can also remove the column (useless?) id .

+2
source 
 SELECT SUM(balance) FROM your_table GROUP BY userid

Should work, but a comment that corrects the table is really a better approach.

+2
source

You can create a table with the same structure and transfer data with this query

 insert into newPriceTable(id, userid, balance) select u.id, p.userid, sum(balance) as summation from price p join ( select userid, min(id) as id from price group by userid ) u ON p.userid = u.userid group by p.userid

Play around the request here: http://sqlfiddle.com/#!2/4bb58/2

+1
source

The work is mostly done in MSSQL, but you should be able to convert the syntax.

Using the GROUP BY custom identifier, you can SUM () Balance, attach it back to your main table to update the balance for all duplicates. Finally, you can use RANK () to order duplicate Userids and keep only the earliest values.

I would select all of this in the new table, and if it looks good, reduce the old table and rename it.

http://sqlfiddle.com/#!3/068ee/2

0
source

More articles:


All Articles