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

Matt Warden mwarden at gmail.com
Mon Jan 8 12:15:44 CST 2007


On 1/8/07, jason h <jayuk_1 at hotmail.com> wrote:
> I have a table like the following:
>
> recording_id   |   quantity
> 1                          1
> 2                          1
> 2                          3
> 1                          3
> 3                          3
> 4                          2
> 5                          2
> 2                          2
>
> and what I would like is to create a new table that would look like:
>
> recording_id   |   quantity
> 2                          6
> 1                          4
> 3                          3
> 4                          2
> 5                          2
>
> 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.
>
> I've tried a lot of ways and scoured the web for a solution but nothing
> seems to work, hope you can help!

Are you having trouble with the query itself? Looks like:

select recording_id, sum(quantity)
from table
group by recording_id

If its the second part of the process that you're stuck on, take a
look at INSERT... SELECT syntax:

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list