[thelist] MYSQL - Joining multiple rows and adding a columns values

Brian Cummiskey brian at hondaswap.com
Mon Jan 8 12:20:58 CST 2007

jason h wrote:
> What i'm basically trying to do is to create a new table from the 
> existing table, with joined rows that have the same recording_id, and 
> add the quantitys together. The order of the rows isn't important.

create your new table...

then run this:

insert into NEWTABLE (
SELECT `recording_id` , sum( `quantity` )
GROUP BY recording_id

Note: your mysql version  must be up to date to run the inline query 
like this.

The group by on only one field is considered bad practice, so test 
before you do the actual insert.  on my sample table, this worked fine 

More information about the thelist mailing list