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 
                | 2012-01-17 15:06:46 |
| 2346 | 41.135.155.245 |    3 | MWEB CONNECT (PROPRIETARY) LIMITED 
                | 2012-01-17 15:15:09 |

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:
> 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
>                | 2012-01-17 15:06:46 |
> | 2346 | 41.135.155.245 |    3 | MWEB CONNECT (PROPRIETARY) LIMITED
>                | 2012-01-17 15:15:09 |
> 
> 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?
> 

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:
> On 18-01-2012 17:47, The Natural Philosopher wrote:
>> 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
>>                | 2012-01-17 15:06:46 |
>> | 2346 | 41.135.155.245 |    3 | MWEB CONNECT (PROPRIETARY) LIMITED
>>                | 2012-01-17 15:15:09 |
>>
>> 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?
>>
> 
> not enough info....
> 
> but if the columns in above data are named
> { id, ip, counts, text, date }
> 
Near enough :-)

> 
> than:
> SELECT ip, sum (counts), max(date)
> FROM .....
> GROUP BY ip;
> 
> should be close to you answer.
> 

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:
> Luuk wrote:
>> On 18-01-2012 17:47, The Natural Philosopher wrote:
>>> 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
>>>                | 2012-01-17 15:06:46 |
>>> | 2346 | 41.135.155.245 |    3 | MWEB CONNECT (PROPRIETARY) LIMITED
>>>                | 2012-01-17 15:15:09 |
>>>
>>> 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?
>>>
>>
>> not enough info....
>>
>> but if the columns in above data are named
>> { id, ip, counts, text, date }
>>
> Near enough :-)
> 
>>
>> than:
>> SELECT ip, sum (counts), max(date)
>> FROM .....
>> GROUP BY ip;
>>
>> should be close to you answer.
>>
> 
> That gets the required set, but how to replace it in the same table?
> 
> Use a temp table and copy it back?
> 

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