EXPERT RESPONSE
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
|
