OraFAQ Forum: SQL & PL/SQL » comparing sum values

Home » SQL & PL/SQL » SQL & PL/SQL » comparing sum values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comparing sum values [message #215992]

Wed, 24 January 2007 09:54

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.

Report message to a moderator

Re: comparing sum values [message #215996 is a reply to message #215992]

Wed, 24 January 2007 10:13

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: comparing sum values [message #216015 is a reply to message #215992]

Wed, 24 January 2007 11:45

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Re: comparing sum values [message #216037 is a reply to message #215992]

Wed, 24 January 2007 15:01

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.

Report message to a moderator

Previous Topic:	Update all the retrieved records at once (meged)
Next Topic:	indexes

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 03:19:21 CST 2024