...

View Full Version : Merging and Combining



michaelespinosa
01-20-2010, 11:25 PM
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.

JAY6390
01-20-2010, 11:30 PM
Do you want a query to pull out the data like that or help with creating a table to hold it?

michaelespinosa
01-20-2010, 11:46 PM
A query to pull that data out. Then I can put it another table if I want.

-Thanks

JAY6390
01-21-2010, 12:03 AM
SELECT
name,
address,
SUM(amount) AS `total`,
COUNT(amount) AS `number`
FROM
`table`
GROUP BY
name,
address

michaelespinosa
01-21-2010, 07:07 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum