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

Brooking, John John.Brooking at sappi.com
Mon Jan 8 12:43:11 CST 2007

From: "jason h" <jayuk_1 at hotmail.com>
> 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
> 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
> seems to work, hope you can help!

Have you learned about the GROUP BY clause? If I understand what you
want, it should be a pretty simple query with that and the SUM function.
You can still order the results with ORDER BY, using column numbers if
you want to order by the SUM function result.

Don't know whether you can also create and populate a new table in the
same statement. It depends on what database you are using. But I'm not
sure why you need it in a new table anyway, unless you want to create a
reporting table from a very large detail table.

- John

This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.

More information about the thelist mailing list