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 to be able to delete the other duplicate rows containing the duplicate names. What can I do? Any suggestions?

Answer 1

I think easiest way to do it in one query would be using common table expressions:

with cte as (
    delete from stats
    where lower(nick) in (
      select lower(nick) from stats group by lower(nick) having count(*) > 1
    )
    returning *
)
insert into stats(nick, totalgames, wins)
select lower(nick), sum(totalgames), sum(wins)
from cte
group by lower(nick);

As you see, inside the cte I'm deleting duplicates and returning deleted rows, after that inserting grouped deleted data back into table.

see sql fiddle demo