Home » SQL & PL/SQL » SQL & PL/SQL » comparing sum values
comparing sum values [message #215992] Wed, 24 January 2007 09:54 Go to next message
chris81
Messages: 4
Registered: January 2007
Junior Member
Hi:

I wrote this code that gives displays the total mileage per car and total mileage per contract:

COLUMN ContractNo FORMAT 099 HEADING 'Contract No'
COLUMN CarReg FORMAT A9 HEADING 'Car Reg' JUSTIFY LEFT
COLUMN DriverNo FORMAT 9999 HEADING 'DriverNo'
COLUMN MILEAGESTART FORMAT 9999999 HEADING 'Start Mileage'
COLUMN MILEAGEEND FORMAT 9999999 HEADING 'End Mileage'

BREAK ON ContractNo SKIP 1 ON CarReg ON REPORT
COMPUTE SUM OF TOTAL_MILES ON ContractNo REPORT

SELECT ContractNo, CarReg, DriverNo, MILEAGESTART, MILEAGEEND, (MILEAGEEND - MILEAGESTART) TOTAL_MILES
FROM Contract_Car;

My Question is this is there a way I can compare the Sum total against in my case a total mileage allowed field in a where statement?

Thanks for any help.
Re: comparing sum values [message #215996 is a reply to message #215992] Wed, 24 January 2007 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>My Question is this is there a way I can compare the Sum total against in my case a total mileage allowed field in a where statement?
What "WHERE" statement? I guess I am going blind. I only see "WHERE" in your question.
Re: comparing sum values [message #216015 is a reply to message #215992] Wed, 24 January 2007 11:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You would need to use the SQL SUM function instead of computing a SQL*Plus SUM. Once you have that sum in a sql query, you can use it in a having clause in the same query or in a where clause in an outer query.
Re: comparing sum values [message #216037 is a reply to message #215992] Wed, 24 January 2007 15:01 Go to previous message
chris81
Messages: 4
Registered: January 2007
Junior Member
Thanks to everyone who replied with your help I managed to come up with a solution to my problem.
Previous Topic: Update all the retrieved records at once (meged)
Next Topic: indexes
Goto Forum:
  


Current Time: Sat Dec 03 14:24:37 CST 2016

Total time taken to generate the page: 0.07949 seconds