Need help with SQL selection statement.

Hello:

For quite some time now I've always wanted to do something like:

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')

where  emp.employee_id = pay.employee_id
and    pay.code='GROSS_PAY'
and    pay.pay_number between '9401' and '9424'

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.

How do I get around this sort of thing? Is "new_value" or something like that involved? In the past, I've resigned myself to coding these types of things in RPT, but surely there must be an easier and quicker way to do this just using SQL?

Any information would be greatly appreciated.

                        
                                                     
------------------------------------------------------------
Steve Frampton        E-mail: <frampton_at_vicuna.ocunix.on.ca>