On Tue, 10 Mar 2009, Erik Smith wrote: > I am new to SQLite and am trying to automatically create a secondary table > which my python app will query against. The secondary table is a summary of > the 1st table by specific types. I have looked at stored procedures (but > sqlite does not support these) and triggers with no success. Any > recommendations? Here is a sample of what I am trying to do: > Existing Table: > > 234|John|Smith|1.2|catA > 234|John|Smith|.8|catA > 234|John|Smith|1|catB > 234|John|Smith|5|catC > 234|John|Smith|.2|catD > 234|John|Smith|.8|catD > 567|Jim|Jones|1|catA > 567|Jim|Jones|2|catB > 567|Jim|Jones|3|catC > 567|Jim|Jones|4|catD > 890|Jane|Mickey|1|catA > 890|Jane|Mickey|4|catA > 890|Jane|Mickey|1|catB > 890|Jane|Mickey|4|catC > 890|Jane|Mickey|6|catC > > New table should have: > > 234|John|Smith|2|catA > 234|John|Smith|1|catB > 234|John|Smith|5|catC > 234|John|Smith|1|catD > 567|Jim|Jones|1|catA > 567|Jim|Jones|2|catB > 567|Jim|Jones|3|catC > 567|Jim|Jones|4|catD > 890|Jane|Mickey|5|catA > 890|Jane|Mickey|1|catB > 890|Jane|Mickey|10|catC
Assuming the names of the columns in the 1st table are: id, fn, ln, dataval, and categ, then this qry will summarize it as in your example: select id, fn, ln, sum(dataval) as sum, categ group by categ; This query could serve as your 'secondary table', as you can query against it, e.g., select id, sum from (select id, fn, ln, sum(dataval) as sum, categ group by categ); Or, you can create a view from the first query, like a virtual table, and then just refer to the view as a secondary table http://www.sqlite.org/lang_createview.html Chris > > Thanks. > > Erik > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
