On 08/10/2007, Daan van der Sanden <[EMAIL PROTECTED]> wrote: > Hi, > > Is it possible to merge two rows with SQLite? Say I have the following table": > CREATE TABLE foo(a integer, b integer, c integer, d integer); > CREATE INDEX idx ON foo(a,b); > > With the following data: > > INSERT INTO foo VALUES (1,1,1,2); > INSERT INTO foo VALUES (1,2,3,4); > INSERT INTO foo VALUES (1,2,5,6); > INSERT INTO foo VALUES (2,1,7,8); > INSERT INTO foo VALUES (3,1,9,10); > INSERT INTO foo VALUES (3,2,11,12); > INSERT INTO foo VALUES (3,2,13,14); > INSERT INTO foo VALUES (3,3,15,16); > > What I would like is that when the combenation of a and b are not unique to > merge the two rows to one and sum the values b and c. So the resulting table > should become: > > a | b | c | d > ---+---+---+---+ > 1 | 1 | 1 | 2 > 1 | 2 | 8 | 10 > 2 | 1 | 7 | 8 > 3 | 1 | 9 | 10 > 3 | 2 | 24| 26 > 3 | 3 | 15| 16 > > I know how to select the two duplicate rows (ensum them, but. I use the query: > SELECT * FROM foo GROUP BY a,b HAVING count(*)>1; to select them. > > But how do I merge them? Do I need a self written program to itterate throuw > the result of the previous query to merge them or can this be done > on the sqlite3 program with a few "simple" querys? > > Thanks in advance! > Kind Regards > Daan
Hi Daan, sqlite> select a,b,sum(c),sum(d) from foo group by a,b; gives you the data you are after. This could be used to populate another table via sqlite> insert into newFoo select a,b,sum(c),sum(d) from foo group by a,b; Of course, if you can get the data you want from your existing table using a simple query, you may not actually need a new table. Rgds, Simon ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
