Ask the Expert

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 

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: