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 last published in April 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close