Newsgroups: comp.databases.oracle From: frampton@vicuna.ocunix.on.ca (Steve Frampton) Subject: Need help with SQL selection statement. Reply-To: frampton@vicuna.ocunix.on.ca Message-ID: <542wZc1w165w@vicuna.ocunix.on.ca> Organization: Vicuna Systems Company, Kingston, Ontario, CANADA Date: Thu, 2 Feb 1995 17:10:27 -0500 X-Received: by usenet.pa.dec.com; id AA18441; Thu, 2 Feb 95 19:30:36 -0800 X-Received: by pobox1.pa.dec.com; id AA26484; Thu, 2 Feb 95 19:30:30 -0800 X-Received: from uunet.ca by inet-gw-3.pa.dec.com (5.65/10Aug94) id AA21407; Thu, 2 Feb 95 19:16:00 -0800 X-Received: from ocunix by mail.uunet.ca with UUCP id <91728-1>; Thu, 2 Feb 1995 19:44:16 -0500 X-Received: by micor.OCUnix.On.Ca (smail2.5) id AA17709; 2 Feb 95 19:31:06 EST (Thu) X-Received: by vicuna.ocunix.on.ca (1.65/waf) via UUCP; Thu, 02 Feb 95 18:08:20 EST for comp.databases.oracle.usenet@decwrl.dec.com X-To: comp.databases.oracle.usenet@decwrl.dec.com X-Comments: System Operator X-Content-Type: text/plain; charset=us-ascii X-Mailer: Waffle [version 1.65 DOS] Lines: 34 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') 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. 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: