OraFAQ Forum: SQL & PL/SQL » SQL Query (should be pretty easy ;-)

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (should be pretty easy ;-)

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

SQL Query (should be pretty easy ;-) [message #2232]

Mon, 01 July 2002 06:02

Mike Nibeck
Messages: 49
Registered: May 2002

Member

Given a payments table that has the following:

Date Pay_Gross Pay_Net
---- --------- -------
1/1/01 $1134 $945
2/1/01 $1200 $965
3/1/01 $1198 $950
...
...

Need a qeury that will return:

1 - Total Gross pay
2 - Total taxes paid (total gross - total net)
3 - Lat payment date
4 - Last payment amount (based on last date date)

Oracle 8i

Thanks,

Mike

Report message to a moderator

Re: SQL Query (should be pretty easy ;-) [message #2237 is a reply to message #2232]

Mon, 01 July 2002 06:53

Balaji
Messages: 102
Registered: October 2000

Senior Member

Hi try this

SQL> DESC PAYMENT
Name Null? Type
------------------------------- -------- ----
DATE1 DATE
PAY_GROSS NUMBER
PAY_NET NUMBER

SQL> SELECT * FROM PAYMENT
2 ;

DATE1 PAY_GROSS PAY_NET
--------- --------- ---------
01-JAN-01 1134 945
01-FEB-01 1200 965
01-MAR-01 1198 950

SQL> SELECT TO_CHAR(SUM(PAY_GROSS),'$9999')"Gross Pay" FROM PAYMENT;

Gross
------
$3532

SQL> SELECT TO_CHAR(SUM(PAY_GROSS-PAY_NET),'$9999')"TOTAL TAX PAID" FROM PAYMENT;

TOTAL
------
$672

SQL> SELECT MAX(DATE1)FROM PAYMENT;

MAX(DATE1
---------
01-MAR-01

SELECT to_char(PAY_GROSS,'$9999')"Gross",to_char(PAY_NET,'$9999')"Net",to_char(pay_gross-pay_net,'$9999')"Tax Paid" from payment where
date1 in (select max(date1)from payment;

TO_CHA TO_CHA Tax Pa
------ ------ ------
$1198 $950 $248

rgds
Balaji

Report message to a moderator

Re: SQL Query (should be pretty easy ;-) [message #2258 is a reply to message #2232]

Tue, 02 July 2002 08:52

Mike Nibeck
Messages: 49
Registered: May 2002

Member

Excellet response, thank. Works very well.

One last question. I can find the last payment by using MAX(Date), is there anyway to find the amount of that payment?

Report message to a moderator

Re: SQL Query (should be pretty easy ;-) [message #2275 is a reply to message #2232]

Wed, 03 July 2002 06:33

Balaji
Messages: 102
Registered: October 2000

Senior Member

select the column with the where condition as the max(date)

rgds
Balaji

Report message to a moderator

Re: SQL Query (should be pretty easy ;-) [message #2285 is a reply to message #2232]

Wed, 03 July 2002 11:14

Mike Nibeck
Messages: 49
Registered: May 2002

Member

Balaji, I really appreciatre you help. I think someday I might get the hang of this SQL thing ;-)
Anyway, your answer makes sense, but I'm not 100% sure how I would handle it in my exact situation

The first query that I had, using the MAX, and SUM values were loaded into a refrence cursor that is being passed as an output param to some .NET code.

How would I do another SELECT on the Payment field using the MAX(Date), and append it to that same cursor? I need to some how do it in the same select statement.

Did that make any sense?

- Mike

Report message to a moderator

Previous Topic:	Changing Data Types
Next Topic:	Partitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:12:07 CDT 2024