| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: subtracting variables in oracle
Thanks. I accidently posted this entry twice and someone else replied to that one too. His reply was as follows:
create view t_v as
select lastname, firstname, title, department,
nvl(sum(decode(pay_type, 'Gross', amount, 0)),0) -
nvl(sum(decode(pay_type, 'Bonus', amount, 0)),0) gross_no_bonus
from yourtable
This seemed to work. I haven't tried your solution, I will though. Thanks again.
In article <3A6D7379.3FEF_at_yahoo.com>,
connor_mcdonald_at_yahoo.com wrote:
> Connor McDonald wrote:
> >
> > pankrateon_at_my-deja.com wrote:
> > >
> > > the stage: Oracle 8i 8.1.6 a table that contains employee info of
which there
> > > is one column labeled "pay type" The "pay type" can be "Gross" or
"Bonus". An
> > > other field is "Amount"
> > >
> > > table looks like this:
> > >
> > > lastname | firstname | title| department | pay_type | amount
> > >
> > > I want to be able to group by the persons name (last and first)
and then
> > > subract any "Bonus" amount from their "Gross" amount
> > >
> > > I would want to create a view that looks as follows:
> > >
> > > lastname | firstname | title| department | gross - amount
> > >
> > > how do I do that?
> > > is it possible have this displayed as a view?
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> > self-join should work
> >
> > select a.key_fields, a.amount - b.amount
> > from blah a, blah b
> > where a.key_fields = b.key_fields
> >
> > HTH
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk (mirrored at
> > http://www.oradba.freeserve.co.uk)
> >
> > "Some days you're the pigeon, some days you're the statue"
>
>
>
>
Sent via Deja.com
http://www.deja.com/
Received on Wed Jan 24 2001 - 08:28:10 CST
 |
 |