SQL: How to merge case-insensitive duplicates

Question

What would be the best way to remove duplicates while merging their records into one? I have a situation where the table keeps track of player names and their records like this: ``` stats ------------------------------- nick totalgames wins ... John 100 40 john 200 97 Whistle 50 47 wHiStLe 75 72 ... ``` I would need to merge the rows where nick is duplicated (when ignoring case) and merge the records into one, like this: ``` stats ------------------------------- nick totalgames wins ... john 300 137 whistle 125 119 ... ``` I'm doing this in Postgres. What would be the best way to do this? I know that I can get the names where duplicates exist by doing this: ``` select lower(nick) as nick, totalgames, count(*) from stats group by lower(nick), totalgames having count(*) > 1; ``` I thought of something like this: ``` update stats set totalgames = totalgames + s.totalgames from (that query up there) s where lower(nick) = s.nick ``` Except this doesn't work properly. And I still can't seem…

Accepted Answer

SQL Fiddle

Here is your update:

```
 UPDATE stats
 SET totalgames = x.games, wins = x.wins
 FROM (SELECT LOWER(nick) AS nick, SUM(totalgames) AS games, SUM(wins) AS wins
     FROM stats
      GROUP BY LOWER(nick) ) AS x
 WHERE LOWER(stats.nick) = x.nick;
```

Here is the delete to blow away the duplicate rows:

```
 DELETE FROM stats USING stats s2
 WHERE lower(stats.nick) = lower(s2.nick) AND stats.nick < s2.nick;
```

(Note that the 'update...from' and 'delete...using' syntax are Postgres-specific, and were stolen shamelessly from this answer and this answer.)

You'll probably also want to run this to downcase all the names:

```
 UPDATE STATS SET nick = lower(nick);
```

Aaaand throw in a unique index on the lowercase version of 'nick' (or add a constraint to that column to disallow non-lowercase values):

```
CREATE UNIQUE INDEX ON stats (LOWER(nick)); 
```

SQL: How to merge case-insensitive duplicates

Solution

Problem

Related problems