Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Mar 2009
    Location
    Michigan
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Any way to combine duplicates?

    For example, if I have a table like so:
    Code:
    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:
    Code:
    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:
    Code:
    '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:
    1. How do I sort duplicates in a table?
    2. 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.

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    Code:
    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.

    Code:
    select count(*)
            account
    from table_name
    group by account
    or

    Code:
    select t1.account
    from table name t1
      select ( count(*) 
               from table_name t2 
               where t2.account = t1.account          
               )
    edumacated guess.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    New Coder
    Join Date
    Mar 2009
    Location
    Michigan
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    I don't see why you need the ip then.

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

    Code:
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #5
    New Coder
    Join Date
    Mar 2009
    Location
    Michigan
    Posts
    26
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •