Re: Select question

Koos Schut wrote: > > I have a question about a general problem(?) I have a database which > is something as: > nr amountA amountB > A 100 70 > B 110 75 > C 80 60 > D 90 50 > > What I want is to substract amountB for nrs C and D from amountA > for nrs A and B: something like > select sum(first.amountA - second.amountB) > from table first, table second > where first.nr in (A,B) and second.nr in (C,D) > > I want the outcome 100 + 110 - 60 - 50, > I get the outcome 100 + 110 - 60 - 50 + 100 + 110 - 60 - 50: > everything in (A,B) gets counted as often as it can be combined with > everything in (C,D) and the other way around. > > I can eliminate one double counting with the use of 'distinct'. How > can I eliminate all double countings? (Especially when I combine > (A,B,C,D,E) with (F,G,H) or more complex.) Anything for someone named "Koos": Since you don't really want to join the tables, and you have seperate data sets for the rows you're trying to subtract, the following should work in all complex cases: SELECT (SELECT SUM(amountA) FROM table WHERE nr IN (A,B)) - (SELECT SUM(amountB) FROM table WHERE nr IN (C,D)) FROM table WHERE nr = A (the WHERE nr = A clause for the outer select does what "DISTINCT" would do; makes sure you only do the inner selects once (nr is assumed to be unique). //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 5299 DTC Blvd Suite 740 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-843-6408 and do not necessarily //////////////// http://www.informix.com reflect those of my employer