[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