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.
Tom
BareFeet
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
