Q

Adding profits for each fish type with PL/SQL

Using PL/SQL, I have a few tasks, and rather than further struggle through it, I'd rather see if you can help and then study your answer...

SQL> desc sftxn_2 
----------- 
SC_ID     NOT NULL VARCHAR2(10) 
SF_ID     NOT NULL VARCHAR2(10) 
AMT       NOT NULL NUMBER(4,2) 
TX_TIME   NOT NULL DATE 
Assuming the yearly overhead paid to cover the price of fishing equipment and bait is 10% of total catch, calculate the profit realized for each fish type for each year, and then add these up for the entire profit for each year.
sc_id is salesclerk id 
sf_id is fish id 
amt is amount sold 

In other words, you want me to write the code for you, right? :)

Here's some sample data:

SQL> select * from sftxn_2 ;

SC_ID      SF_ID             AMT TX_TIME
---------- ---------- ---------- ---------
1          1                  99 11-APR-03
1          2                  50 11-APR-03
2          1                  75 11-APR-03
3          2                  25 11-APR-03
2          3                  95 11-APR-03
3          4                  90 11-APR-03
4          3                  50 11-APR-03
5          4                  80 11-APR-03
A single SELECT statement can give you the info data you want:
SQL> select to_char(tx_time, 'YYYY') yr,
  2         sf_id the_fish,
  3         sum(amt) total_amt,
  4         sum(amt * .1) total_cost,
  5         sum(amt - (amt * .1) ) tot_profit
  6  from sftxn_2
  7* group by cube (to_char(tx_time, 'YYYY'), sf_id)
SQL> /

THE_ THE_FISH    TOTAL_AMT TOTAL_COST TOT_PROFIT
---- ---------- ---------- ---------- ----------
2003 1                 174       17.4      156.6
2003 2                  75        7.5       67.5
2003 3                 145       14.5      130.5
2003 4                 170         17        153
2003                   564       56.4      507.6
     1                 174       17.4      156.6
     2                  75        7.5       67.5
     3                 145       14.5      130.5
     4                 170         17        153
                       564       56.4      507.6
The rows where THE_FISH column has a null values show the grand total for the year. The rows where the YR column has a null value show the grand total for a given fish for the year.

You can use this in a cursor and process each row individually as you desire.

For More Information


This was first published in April 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close