Yes, but I want to do them in one shot. Igor's solution worked perfectly.
josé ----- Original Message ----- From: "P Kishor" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Monday, February 11, 2008 6:12 PM Subject: Re: [sqlite] Selecting all and some columns > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
