homepage Welcome to WebmasterWorld Guest from 54.237.77.181
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Combine Sum Total
airpirate




msg:4437212
 8:23 pm on Apr 4, 2012 (gmt 0)

I have a problem that I need help with. I have a MySQL table with name, address, email, and quantity. In this table there are multiple rows where the name, address, and email are the same but the quantity may vary. I need to make a table with the unique name, address, and emails but have the total sum of the quantity for each of those unique name, address, emails. For instance if I have a table with;

Fred, 123rd St, fred@fred.org, 4
Fred, 123rd St, fred@fred.org, 3
Fred, 123rd St, fred@fred.org, 1
George, 489 S. St., george@george.com, 5
George, 489 S. St., george@george.com, 4
George, 489 S. St., george@george.com, 2

I need it to be in one table;
Fred, 123rd St, fred@fred.org, 8
George, 489 S. St., george@george.com, 11

I know how to move the distinct name, address, and email into another table. I just don't know how to add up the sum of the quantity for each unique name, address, and email and insert that into the other table.

I used

INSERT INTO tbl2 (name, address, email) SELECT DISTINCT name, address, email from tbl1;

to remove the duplicates. Can anyone give me an idea to help me add up the sums and insert in to the new table?

Your help would be greatly appreciated.

 

Dijkgraaf




msg:4437277
 10:50 pm on Apr 4, 2012 (gmt 0)

Do a search for the SQL term GROUP BY

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved