Re: [sqlite] Selecting all and some columns
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The PM is the same for the same ProjID because the split is a one to > one > assignment, so that should not happen. So, if there is a case like > this, > the PM has been set wrong. What I would like to have is a total of > the > ProjFund, ProjFund total - invoice total where Split = 'y', invoice > total > where Split = 'y', the first (or last or any of the) PM that Split = > 'y'. 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), max(case split when 'y' then PM else '' end) from ClassTable group by Class, ProjID; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"Igor Tandetnik" wrote... > "jose isaias cabrera" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] >> 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'. > > In the previous problem, you got one row for each distinct Class/ProjId > pair. Do you now want to group by Class/ProjId/PM tuple? If not, and you > still want to group by Class/ProjId, what should happen if, within this > group, there are two different PMs both marked with Split='y'? For > example, what resultset do you expect for input like this: > > Class|ProjID|ProjFund|Invoice|PM|Split > Finishers|1045|75|75|JIC|y > Finishers|1045|75|30|LED|y The PM is the same for the same ProjID because the split is a one to one assignment, so that should not happen. So, if there is a case like this, the PM has been set wrong. What I would like to have is a total of the ProjFund, ProjFund total - invoice total where Split = 'y', invoice total where Split = 'y', the first (or last or any of the) PM that Split = 'y'. thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
Hi José, > 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||| Building on the normalized version of the database that I posted earlier, you would add a table: create table FundPM ( FundID integer--> Fund.FundID , PM text collate nocase ) ; insert into FundPM ( FundID, PM ) values (1, 'JIC' ); insert into FundPM ( FundID, PM ) values (2, 'LED' ); insert into FundPM ( FundID, PM ) values (3, 'SAN' ); insert into FundPM ( FundID, PM ) values (4, 'JIC' ); insert into FundPM ( FundID, PM ) values (5, 'ELI' ); insert into FundPM ( FundID, PM ) values (6, 'ELI' ); insert into FundPM ( FundID, PM ) values (8, 'JIC' ); insert into FundPM ( FundID, PM ) values (9, 'LED' ); insert into FundPM ( FundID, PM ) values (10, 'KAP' ); insert into FundPM ( FundID, PM ) values (11, 'ELI' ); > I would like to also get the PM value when split = 'y'. Again, you just join the Split table to the columns that you want: select PM from Split left join Invoice on Split.InvoiceID = Invoice.InvoiceID left join FundPM on Invoice.FundID = FundPM.FundID ; I think this has reached the point where it's probably useful for you to explain the bigger picture of your data and what you're trying to do with it. Tom BareFeet -- ADSL2+ at the cheapest price in Australia: http://www.tandb.com.au/broadband/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"jose isaias cabrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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'. In the previous problem, you got one row for each distinct Class/ProjId pair. Do you now want to group by Class/ProjId/PM tuple? If not, and you still want to group by Class/ProjId, what should happen if, within this group, there are two different PMs both marked with Split='y'? For example, what resultset do you expect for input like this: Class|ProjID|ProjFund|Invoice|PM|Split Finishers|1045|75|75|JIC|y Finishers|1045|75|30|LED|y Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
Hi José, >> 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? I wouldn't think so, no. This method (ie normalizing first) means that all the rows that have a "Split" value are already in one small table. So there's no searching through all the rows in the one huge table that's filled mainly with nulls. The joining with the Fund and Invoice data is done via the indexed primary key fields, a procedure for which SQL database engines are optimized. In short, if you read the first chapter of any book on SQL programming, you'll see that normalizing your database is the fundamental first step in achieving the nest results with minimum wastage. Tom BareFeet -- 5000 computer accessories delivered anywhere in Australia: http://www.tandb.com.au/forsale/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
"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
Re: [sqlite] Selecting all and some columns
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 projfundinvoice split > -- -- -- -- -- > Finishers 104573 > Finishers 104575 30 > Finishers 104575 30 > Finishers 104575 30 > Finishers 104575 > Finishers 104575 75 y > Finishers 104575 25 > Finishers 104573 > Finishers 104573 > Finishers 104573 > Finishers 104573 58.4y > Finishers 104573 > 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 14814.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 14814.6133.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
Re: [sqlite] Selecting all and some columns
"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
Re: [sqlite] Selecting all and some columns
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting all and some columns
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. 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; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 projfundinvoice split -- -- -- -- -- Finishers 104573 Finishers 104575 30 Finishers 104575 30 Finishers 104575 30 Finishers 104575 Finishers 104575 75 y Finishers 104575 25 Finishers 104573 Finishers 104573 Finishers 104573 Finishers 104573 58.4y Finishers 104573 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 14814.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 14814.6133.4 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting all and some columns
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. thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
