...

View Full Version : Any way to combine duplicates?



Shinykirby
03-14-2009, 11:07 PM
For example, if I have a table like so:

ID | Name | Amount
-------------------
1 | Joe | 10
-------------------
2 | Bob | 6
-------------------
3 | Jack | 2
-------------------
4 | Bob | 5
-------------------
5 | Joe | 1
-------------------
6 | Joe | 9
I want to run a query that will combine all the duplicates as well as get the sum(`amount`), so it would display as:

Name | Amount
-------------
Joe | 20
-------------
Bob | 11
-------------
Jack | 2

Note that I don't want to do this manually considering my table has about 20,000 entries right now. I would be using a unique key if I knew how, I wasn't thinking about the potential abuse I was going to get with the system I incorporated. Here is my current table setup:


'records', 'CREATE TABLE `records` (
`id` int(10) unsigned NOT NULL auto_increment,
`account` varchar(20) NOT NULL,
`ip` varchar(15) NOT NULL,
`date` int(10) unsigned NOT NULL,
`times` smallint(2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` USING BTREE (`account`,`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=21305 DEFAULT CHARSET=latin1'

So my two questions are:

How do I sort duplicates in a table?
Why isn't the unique key for `account` and `ip` I have working correctly, and how can I fix that?


Thanks for any help I can get.

bazz
03-15-2009, 02:15 AM
I can't help with it all but, take a look at your unique key in your create table statement.

I think it should be


unique index (account, ip)


However I think you have a problem with it if some people will not have a fixed ip address.

for getting that data you need, try one of these or a version along the line.



select count(*)
account
from table_name
group by account


or



select t1.account
from table name t1
select ( count(*)
from table_name t2
where t2.account = t1.account
)


edumacated guess.

bazz

Shinykirby
03-15-2009, 04:23 AM
Bazz: Thanks for the query, it works well (and as a bonus effect, it tells me how many times the account is in the table with count(*)).

The IP address is not a problem - the script I'm using first searches for account name, then IP, by the last twelve hours. If it's over 12 hours, then it will overwrite the IP and register the new IP to the account name. The idea is that I don't want overlapping IP addresses and account names.

bazz
03-15-2009, 04:42 AM
I don't see why you need the ip then.

You might be better to make the username/account a pk itself.



CREATE TABLE records
( id int NOT NULL auto_increment
, account varchar(20) NOT NULL
, date date NOT NULL
, times smallint(2) NOT NULL
, PRIMARY KEY (account)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


bazz

Shinykirby
03-15-2009, 08:27 AM
The IP is necessary because the users can have multiple accounts, so I'm trying to prevent users from making multiple accounts to abuse the table. (Understandably, I can't block users who will create a new account and access the table through a proxy, but I figure that checking for both IP and account will restrict abuse as much as possible.)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum