maskers maskers - 1 year ago 67
MySQL Question

How can I sum and then remove duplicate fields MySql?

Sorry if this turns out to be duplicate but I am having a hard time finding the exact answer...

I have table that contains 'email'(varchar) and 'clicks'(int).

The email field contains duplicate instances of email values. I would like to remove the duplicates and keep just one instance but also sum all values found in 'clicks' for that given email instance and update the remaining record to reflect that.

I can use distinct and group by to get a view of the records I am after, its the sum and update part I am stumped with.


Answer Source

I don't think you need to use DISTINCT here, but rather you can just group on each email value, taking the sum of clicks as you go along:

SELECT email, SUM(clicks) AS clickSum
FROM yourTable
GROUP BY email

When you mentioned "remaining" record, you neglected to mention how the duplicate records ever got deleted. It would be a bunch of work to selectively remove all but one duplicate record. I might go about this by just inserting the above query into a new table, deleting the old table, and renaming the new one to the old one:

CREATE TABLE yourNewTable (`email` varchar(255), `clicks` int);
INSERT INTO yourNewTable (`email`, `clicks`)
SELECT email, SUM(clicks)
FROM yourTable
GROUP BY email

DROP TABLE yourTable
ALTER TABLE yourNewTable RENAME TO yourTable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download