I have a table of IP addresses, names associated with them, and the
total times they visit a site and when they last did.

Due to concurrency issues, some are duplicates..eg

2347 | 41.135.155.245 | 1 | MWEB CONNECT (PROPRIETARY) LIMITED
what is the simplest query to run to add the counts together, and
select the latest access date, and then delete the duplicates on this?

On 18-01-2012 17:47, The Natural Philosopher wrote:
not enough info....

but if the columns in above data are named
{ id, ip, counts, text, date }


than:
SELECT ip, sum (counts), max(date)
FROM .....
GROUP BY ip;

should be close to you answer.

--
Luuk

Luuk wrote:

That gets the required set, but how to replace it in the same table?

Use a temp table and copy it back?

On 18-01-2012 21:35, The Natural Philosopher wrote:
same table??????? hmmm

INSERT INTO sametable (ip, counts, text, date)
SELECT ip, sum (counts), concat('Total until: 'max(date)), max(date)
FROM sametable
GROUP BY ip;

followed by an:
DELETE FROM samtable
WHERE Name not like 'Total until:%';



--
Luuk