On 2/11/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > Greetings... > > I know that Puneet will get on my case about the obscurity of the subject > (just kidding), but I am trying to find out if I can do this: Imagine this > table and data... > > 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|| > > What I would like is to have total of ProjFund, a total of ProjFund - > Invoices which Split = 'y' and a total of Invoices which Split = 'y'. > > I know I can do this programatically, but I would like to be able to have > sqlite return the results to me. Is it possible? What I have right now is > this, > > SELECT Class, sum(ProjFund), sum(ProjFund) - sum(invoice), sum(invoices) > from ClassTable group by Class, ProjID; > > I just don-t know how to do the Split = 'y' part. Any help would be greatly > appreciated. >
is this what you are looking for? sqlite> select * from t; 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 sqlite> select class, sum(projfund) from t group by class; class sum(projfund) ---------- ------------- Finishers 888 sqlite> select class, sum(projfund) from t where split = 'y' group by class; class sum(projfund) ---------- ------------- Finishers 148 sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) as a from t where split = 'y' group by class; class sum(projfund) a ---------- ------------- ---------- Finishers 148 14.6 sqlite> select class, sum(projfund), sum(projfund)-sum(invoice) a, sum(invoice) b from t where split = 'y' group by class; class sum(projfund) a b ---------- ------------- ---------- ---------- Finishers 148 14.6 133.4 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
