"Igor Tandetnik" wrote... > jose isaias cabrera 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. > > Perhaps something like this: > > SELECT Class, > sum(ProjFund), > sum(ProjFund) - sum(case split when 'y' then invoice else 0 end), > sum(case split when 'y' then invoice else 0 end) > from ClassTable group by Class, ProjID;
Thanks, Igor. This worked perfectly. I have one more ask for help, imagine almost the same data, Class|ProjID|ProjFund|Invoice|PM|Split Finishers|1045|73||JIC| Finishers|1045|75|30|LED| Finishers|1045|75|30|SAN| Finishers|1045|75|30|JIC| Finishers|1045|75||ELI| Finishers|1045|75|75|ELI|y Finishers|1045|75|25|| Finishers|1045|73||JIC| Finishers|1045|73||LED| Finishers|1045|73||KAP| Finishers|1045|73|58.4|ELI|y Finishers|1045|73||| I would like to also get the PM value when split = 'y'. So, I tried editing Igor's solution in many ways, such as this, SELECT Class, distinct(case split when 'y' then PM else null end), sum(ProjFund), sum(ProjFund) - sum(case split when 'y' then invoice else 0 end), sum(case split when 'y' then invoice else 0 end) from ClassTable group by Class, ProjID, PM; but. though this executes, I am unsuccessful getting the correct data that I need. Again, any help would be greatly appreciate it. And yes, I can do this programmatically, but making a few calls to the DB, but I want to try to get all of these values in one call. thanks, josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
