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.
I don't think you need to use
DISTINCT here, but rather you can just group on each
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