Dear experts,
I'm using Oracle 11g Enterprise edition
I need total number of txntype and its failure,success count and total success count amount
CREATE TABLE TRAN_LOG
(
TXNTYPE VARCHAR2(20),
TXNSTATUS CHAR(1),
AMOUNT NUMBER(10,2)
);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('FT','S',100);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('FT','S',500);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('FT','F',1000);
insert into TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)values('FT','F',100);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('A1','S',1000);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('A1','S',500);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('A1','F',100);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('A1','F',50);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('ST','S',1000);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('ST','S',500);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('ST','F',100);
INSERT INTO TRAN_LOG(TXNTYPE,TXNSTATUS,AMOUNT)VALUES('ST','F',50);
commit;
select * from tran_log;
| TXNTYPE | TXNSTATUS | AMOUNT |
| FT | S | 100 |
| FT | S | 500 |
| FT | F | 0 |
| FT | F | 0 |
| A1 | S | 1000 |
| A1 | S | 500 |
| A1 | F | 0 |
| A1 | F | 0 |
| ST | S | 1000 |
| ST | S | 500 |
| ST | F | 0 |
| ST | F | 0 |
Desired output
---------------------------
| TXNTYPE | COUNT | SUCCESS | FAILURE | SUM_AMOUNT |
| FT | 4 | 2 | 2 | 150 |
| A1 | 4 | 2 | 2 | 1500 |
| ST | 4 | 2 | 2 | 1500 |
Note: I have around 100 different TXNTYPE in my table
Please help.