On 11/02/2011, at 1:37 PM, Yuzem wrote: > For example lets say I have the following data: > 1|director|1 > 2|director|2 > 3|director|1 > > In this example the total count for directors is 2, I have two distinct > directors. > In the table "Capacity Statistics" I will have: > director|2 > > The triggers you made add/subtract 1 from "Capacity Statistics" on > insert/delete on "movies people" > What happens if I add the following to "movies people"? > 4|director|2 > > The trigger should add 1 to "Capacity Statistics": > director|3 > > But there are still 2 directors: > 1|director|1 > 2|director|2 > 3|director|1 > 4|director|2
Oh, I see. I was counting the total number of each unique capacity (including director, writers), but you want the number of each unique (capacity, person). No problem. We'll just add a People column to the statistics, and change the uniqueness constraint. Replace my earlier "Capacity Statistics" and triggers with this: begin immediate ; create table "Capacity People Statistics" ( ID integer primary key references "Capacity" (ID) on delete cascade , People_ID integer not null references "People" (ID) on delete cascade , Count integer not null , unique (ID, People_ID) ) ; insert into "Capacity People Statistics" (ID, People_ID, Count) select Capacity_ID, People_ID, count(*) from "Movie People" group by Capacity_ID, People_ID having Count > 0 ; create trigger "Movie People insert" on "Movie People" after insert begin insert or replace into "Capacity People Statistics" (ID, People_ID, Count) select new.Capacity_ID , new.People_ID , ( select coalesce(Count, 0) + 1 from "Capacity People Statistics" where ID = new.Capacity_ID and People_ID = new.People_ID ) ; end ; create trigger "Movie People delete" on "Movie People" after delete begin insert or replace into "Capacity People Statistics" (ID, People_ID, Count) select old.Capacity_ID , old.People_ID , ( select coalesce(Count, 0) - 1 from "Capacity People Statistics" where ID = old.Capacity_ID and People_ID = old.People_ID ) ; end ; create trigger "Movie People update" on "Movie People" after update of Capacity_ID, People_ID begin insert or replace into "Capacity People Statistics" (ID, People_ID, Count) select old.Capacity_ID , old.People_ID , ( select coalesce(Count, 0) - 1 from "Capacity People Statistics" where ID = old.Capacity_ID and People_ID = old.People_ID ) ; insert or replace into "Capacity People Statistics" (ID, People_ID, Count) select new.Capacity_ID , new.People_ID , ( select coalesce(Count, 0) + 1 from "Capacity People Statistics" where ID = new.Capacity_ID and People_ID = new.People_ID ) ; end ; commit ; Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users