"BareFeet" wrote... > Hi Jose, > >> Class|ProjID|ProjFund|Invoice|Split >> Finishers|1045|73|| >> Finishers|1045|75|30| >> Finishers|1045|75|30| >> Finishers|1045|75|30| >> Finishers|1045|75|| >> Finishers|1045|75|75|y >> Finishers|1045|75|25| >> Finishers|1045|73|| >> Finishers|1045|73|| >> Finishers|1045|73|| >> Finishers|1045|73|58.4|y >> Finishers|1045|73|| > > I think in the interests of "normalization" (basically removing > redundancy and nulls), you'd do best to split the data into four tables: > > ProjID |Class > 1045 |Finishers > > Fund: > FundID |ProjID |ProjFund > 1 |1045 |73 > 2 |1045 |75 > 3 |1045 |75 > 4 |1045 |75 > 5 |1045 |75 > 6 |1045 |75 > 7 |1045 |75 > 8 |1045 |73 > 9 |1045 |73 > 10 |1045 |73 > 11 |1045 |73 > 12 |1045 |73 > > Invoice: > InvoiceID|FundID |Invoice > 1 |2 |30 > 2 |3 |30 > 3 |4 |30 > 4 |6 |75 > 5 |7 |25 > 6 |11 |58.4 > > Split: > InvoiceID > 4 > 6 > > Then you only record an entry in Split for those rows for which a > split applies (two rows in this case). > > The above translates into this SQL to create the tables: > > create table Project > ( > ProjID integer primary key > , Class text collate nocase > ) > ; > create table Fund > ( > FundID integer primary key > , ProjID integer --> Project.ProjID > , ProjFund real > ) > ; > create table Invoice > ( > InvoiceID integer primary key > , FundID integer --> Fund.FundID > , Invoice real > ) > ; > create table Split > ( > InvoiceID integer --> Invoice.InvoiceID > ) > ; > > --Populating the tables with your data: > delete from Project; > delete from Fund; > delete from Invoice; > delete from Split; > > insert into Project (ProjID, Class) values (1045, 'Finishers'); > > insert into Fund (ProjID, ProjFund) values (1045, 73); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 75); > insert into Fund (ProjID, ProjFund) values (1045, 73); > insert into Fund (ProjID, ProjFund) values (1045, 73); > insert into Fund (ProjID, ProjFund) values (1045, 73); > insert into Fund (ProjID, ProjFund) values (1045, 73); > insert into Fund (ProjID, ProjFund) values (1045, 73); > > insert into Invoice (FundID, Invoice) values (2, 30); > insert into Invoice (FundID, Invoice) values (3, 30); > insert into Invoice (FundID, Invoice) values (4, 30); > insert into Invoice (FundID, Invoice) values (6, 75); > insert into Invoice (FundID, Invoice) values (7, 25); > insert into Invoice (FundID, Invoice) values (11, 58.4); > > insert into Split (InvoiceID) values (4); > insert into Split (InvoiceID) values (6); > >> --What I would like is to have total of ProjFund > > > select sum(ProjFund) from Fund; > > --> 888.0 > >> --a total of ProjFund - Invoices which Split = 'y' > > > select sum(ProjFund - Invoice) > from Split > left join Invoice on Split.InvoiceID = Invoice.InvoiceID > left join Fund on Invoice.FundID = Fund.FundID > ; > > --> 14.6 > >> --and a total of Invoices which Split = 'y'. > > > select sum(Invoice) > from Split > left join Invoice on Split.InvoiceID = Invoice.InvoiceID > ; > > --> 133.4 > > I hope this helps. The benefits of normalizing increase with the > amount and/or complexity of your data. Notice there are no case > statements to get what you want. You just start with the table you > want (Split, in this case) and join any needed related data, so SQLite > only scans the relevant data, rather than testing every row.
Wouldn't this take longer to process then a case statement? > > Tom > BareFeet > > _______________________________________________ > 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
