Apply aggregate function based on column name in Oracle PL/SQL

Question

I have a table test3 which has the data below. I want to apply aggregate function based on status.

request mkt_val fill_val qty fill_qty rate c_party status
------- ------- -------- --- -------- ---- ------- ------
    IBM     200      100  20       10    1   test1 Accept
    IBM     300      200  30       20    2   test1 Accept
    IBM     400      300  40       30    3   test1 Accept
    IBM     500      400  50       40    4   test2 Reject
    IBM     600      500  60       50    5   test2 Reject

I need output with the columns:

request
a_sum_mkt_val
a_avg_fill_qty
a_min_rate
r_sum_mkt_val
r_avg_fill_qty
r_min_rate
c_cnt (distinct c_party for request)
total_req (total number of records for request)

Prefix a_ for accept, r_ for reject. For the sample data, the output should be:

IBM  900  20  1  1100  45  4  2  5

I am not able to categorise the value based on the status.

It would be very helpful if you could post some DDL for creating your table and inserting data into it. — Armunin
– Armunin, Commented Nov 28, 2013 at 9:30
INSERT INTO test3 VALUES('IBM',200,100,20,10,1,'test2','Reject'); INSERT INTO test3 VALUES('IBM',300,200,30,20,2,'test2','Reject'); INSERT INTO test3 VALUES('IBM',400,300,40,30,3,'test2','Reject'); INSERT INTO test3 VALUES('IBM',500,400,50,40,4,'test2','Reject'); insert into test3 values('IBM',600,500,60,50,5,'test2','Reject'); a_sum means sum FOR accept record r_sum means sum for reject record requets a_sum_mkt_val r_sum_mkt_val IBM 900 1100 — user2862073
– user2862073, Commented Nov 28, 2013 at 9:55

MT0 · Accepted Answer · 2013-11-28 09:48:22Z

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tbl ( request, mkt_val, fill_val, qty, fill_qty, rate,  c_party,  status ) AS
          SELECT 'IBM',     200,      100,  20,       10,    1,   'test1', 'Accept' FROM DUAL
UNION ALL SELECT 'IBM',     300,      200,  30,       20,    2,   'test1', 'Accept' FROM DUAL
UNION ALL SELECT 'IBM',     400,      300,  40,       30,    3,   'test1', 'Accept' FROM DUAL
UNION ALL SELECT 'IBM',     500,      400,  50,       40,    4,   'test2', 'Reject' FROM DUAL
UNION ALL SELECT 'IBM',     600,      500,  60,       50,    5,   'test2', 'Reject' FROM DUAL;

Query 1:

SELECT request,
       SUM( DECODE( status, 'Accept', mkt_val,  NULL ) ) AS a_sum_mkt_val,
       AVG( DECODE( status, 'Accept', fill_qty, NULL ) ) AS a_avg_fill_qty,
       MIN( DECODE( status, 'Accept', rate,     NULL ) ) AS a_min_rate,
       SUM( DECODE( status, 'Reject', mkt_val,  NULL ) ) AS r_sum_mkt_val,
       AVG( DECODE( status, 'Reject', fill_qty, NULL ) ) AS r_avg_fill_qty,
       MIN( DECODE( status, 'Reject', rate,     NULL ) ) AS r_min_rate,
       COUNT( DISTINCT c_party ) AS c_cnt,
       COUNT( 1 ) AS total_req
FROM   tbl
GROUP BY request

Results:

| REQUEST | A_SUM_MKT_VAL | A_AVG_FILL_QTY | A_MIN_RATE | R_SUM_MKT_VAL | R_AVG_FILL_QTY | R_MIN_RATE | C_CNT | TOTAL_REQ |
|---------|---------------|----------------|------------|---------------|----------------|------------|-------|-----------|
|     IBM |           900 |             20 |          1 |          1100 |             45 |          4 |     2 |         5 |

Good, but if you are happy with it then please accept the answer.

Collectives™ on Stack Overflow

Apply aggregate function based on column name in Oracle PL/SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related