Thanks...I will try it out. Erik On Tue, Mar 10, 2009 at 7:16 PM, <cmar...@unc.edu> wrote:
> 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 > -- Ben & Jerry's www.dessertstogo.com (650) 359-9707 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
