Re: [sqlite] Creating a secondary table automatically

[Erik Smith]

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,  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

Re: [sqlite] Creating a secondary table automatically

[cmartin]

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

[sqlite] Creating a secondary table automatically

[Erik Smith]

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

Thanks.

Erik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users