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
    Aug 2007
    Posts
    69
    Thanks
    11
    Thanked 2 Times in 2 Posts

    Question Merging and Combining

    I have a bunch of data in a table that I'm trying to simplify.

    What I have:
    A table with that holds values name, address, amount.
    Jerry Jones, 123 Main Street Sunnyville CA 90210, 10
    Jerry Jones, 123 Main Street Sunnyville CA 90210, 15
    Jerry Jones, 123 Main Street Sunnyville CA 90210, 12
    Jerry Jones, 123 Main Street Sunnyville CA 90210, 13
    Marry Hues, 456 Lowe Drive Cloudyway CA 90120, 14
    Marry Hues, 456 Lowe Drive Cloudyway CA 90120, 8
    Marry Hues, 456 Lowe Drive Cloudyway CA 90120, 10
    Marry Hues, 456 Lowe Drive Cloudyway CA 90120, 15

    What I want:
    The table to reflect the person only once, total the amounts, and count the number of instances.
    Jerry Jones, 123 Main Street Sunnyville CA 90210, 50, 4
    Marry Hues, 456 Lowe Drive Cloudyway CA 90120, 47, 4

    I have added the two additional fields to the table (total_amount and num_inst)

    I'm just not sure how to go about it.

    Thanks in advance for any help.

  • #2
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Do you want a query to pull out the data like that or help with creating a table to hold it?
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #3
    New Coder
    Join Date
    Aug 2007
    Posts
    69
    Thanks
    11
    Thanked 2 Times in 2 Posts
    A query to pull that data out. Then I can put it another table if I want.

    -Thanks

  • #4
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    Code:
    SELECT
        name,
        address,
        SUM(amount) AS `total`,
        COUNT(amount) AS `number`
    FROM
        `table`
    GROUP BY
        name,
        address
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    michaelespinosa (01-21-2010)

  • #5
    New Coder
    Join Date
    Aug 2007
    Posts
    69
    Thanks
    11
    Thanked 2 Times in 2 Posts
    Thanks, that returns exactly what I looking for when I run that query in my db IDE. How do I insert those results into a new table? Sorry if this is basic, I just haven't done this sort of thing yet. I really appreciate the help.


  •  

    Posting Permissions

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