Re: Need help with SQL selection statement.

From: <morleb_at_cbr.hhcs.gov.au>
Date: 8 Feb 95 17:47:38 +1000
Message-ID: <1995Feb8.174738.1_at_cbr.hhcs.gov.au>

In article <542wZc1w165w_at_vicuna.ocunix.on.ca>, frampton_at_vicuna.ocunix.on.ca (Steve Frampton) writes:
> select pay.employee_id, emp.surname,
> sum(pay.amount)-(select sum(ded.amount)
> from pay_records ded
> where ded.employee_id = pay.employee_id
> and ded.code='DEDUCTION')
> from pay_records pay, employee_table emp
> where emp.employee_id = pay.employee_id
> and pay.code='GROSS_PAY'
> and pay.pay_number between '9401' and '9424'
> group by pay.employee_id, emp.surname
> order by emp.surname;
>
> As you can see, I want to total up each employees total pays *for the
> year*, and deduct total deductions *for the year*, and display the
> result.
>
> I can't think of the number of times I've wanted to do something like
> this! But SQL*Plus won't allow me to.

Try this:

select pay.employee_id,emp.surname,

       sum(decode(pay.code,'GROSS_PAY',+1, 'DEDUCTION',-1, 0) 
            * pay.amount)

from pay_records pay, employee_table emp where emp.employee_id = pay.employee_id and (pay.pay_number between '9401' and '9424'

        or pay.code='DEDUCTION')
group by emp.surname, pay.employee_id Received on Wed Feb 08 1995 - 08:47:38 CET