Hi Chris,
Thanks for your help, however, I have tried this out and the problem I have
is the 'view' is only available to the current process -- I think (I am
getting the error message 'unable to open database'). In my app (python), I
call the various tables multiple times which is why I wanted to create a new
table with the summary. From what I have read, a trigger is best suited for
this since it will create the new table in the db -- is this correct or am I
way off base? I have tried the following:
CREATE TRIGGER sum_TBL AFTER INSERT ON TBL
BEGIN
insert into sum_TBL (id,fn,ln,amt,TypeCode)
select id, fn,ln,sum(amt),TypeCode
from TBL
group by id,TypeCode;
END;
But the new table is not created.
Thanks. 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
