[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` )
FROM `OLDTABLE`
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 
though.



More information about the thelist mailing list