# subcontraction

• August 15th, 09:20 PM
Derek
subcontraction
I have the following table

tbl1

ID Amount
1 10
2 5

I want to write a query the returns the following result
set:

Amount_Difference
5

How do I do this?

I would greatly appreciate the help!

Thanks.

Derek
• August 15th, 09:42 PM
Steve
Re: subcontraction
Derek,

If your table contains only two rows, and the IDs are 1 and 2,
a solution is

select (
select Amount from tbl1 where ID = 1
) - (
select Amount from tbl1 where ID = 1
) as Amount_Difference

If you are looking for a general solution that returns some kind
of differences in a larger table or for arbitrary ID values, it would
help if you give an example table with more rows and the result
you want for that table.

-- Steve Kass
-- Drew University
-- Ref: 066C1660-21A6-463C-91C3-82EA7DCCFC56

Derek Ruesch wrote:

• August 15th, 09:57 PM
Delbert
Re: subcontraction
Here is another way.

select
(select amount from tbl1 where [ID] = 1)
-
(select amount from tbl1 where [ID] = 2)

Here is something that gives more answers.
select
tbl1a.[ID],
tbl1b.[ID],
tbl1a.[Amount] - tbl1b.[Amount] as Amount_Difference
from tbl1 tbl1a cross join tbl1 tbl1b
order by tbl1a.[ID],tbl1b.[ID]

Bye,
Delbert Glass

"Delbert Glass" <com> wrote in message
news:phx.gbl...
>
>[/ref]