Re: Select question

Koos Schut wrote: > > Hi all, > > 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.) > -- > > All replys will be appreciated a lot. > Mazzel, cheers, bye, tabe, moi, tot ziens, have a nice day, > Koos > ____________________________________________________________________ > JJ Schut | e-mail: schut@th.rug.nl | > Institute for Theoretical Physics | | > University of Groningen | Phone: +31 50 5999905 | > P.O. Box 800 | Phone: +31 50 3138172 | > 9700 AV Groningen, The Netherlands. | Fax: +31 50 5999984 | > | Yell: Very, Very loud | > -------------------------------------------------------------------- Hi Koos, For the special case mentioned above you can use the following statement select sum(a.amount_a - b.amount_b) from table a , table b where (a.nr = 'A' and b.nr = 'C') or (a.nr = 'B' and b.nr = 'D') This solution won't help you in the more complex cases, of course. I think you can not do them within one SELECT statement. The solution I have found looks like this: Step 1) select sum(amount_a) suma ,0 sumb from table where nr in ('A','B','C','D','E') into temp tmpsum Step 2) update tmpsum set sumb = (select sum(amount_b) from table where nr in ('F','G','H') ) Step 3) select suma - sumb from tmpsum Step 4) drop table tmpsum Hope this helps. Cheers, Dirk --------------------------------------------------- Dirk Schmidtpott S I E M E N S EMail: schmd@dbm212.pdb.sni.de ============= Phone: 0049-5251-8-14922 N I X D O R F Fax : 0049-5251-8-27905 The above statements and opinions are my own and do not necessarily represent those of my employer. ---------------------------------------------------