Select distinct trans_id , to_char(trans_date,'MON') as TRANS

trans_id trans_date trans_amt debit_credit_indicator
001 01-JAN-13 1099 cr
001 12-JAN-13 500 db
002 24-FEB-13 400 db
002 23-MAR-13 345 cr
001 18-APR-13 800 cr
002 15-MAR-13 600 db
001 12-FEB-13 200 cr

i want like this output
trans_id trans_date trans_amt debit_credit_indicator
001 JAN 1599 cr

no.of trans
2

i want trans_id and trans_date like 'JAN' or 'FEB' etc,
trans_amt i want all credit amount - debit amount per each
trans_id. and debit_credit_indicator and no.of transactions
in a month.

Question Posted / vinay

1 Answers
5443 Views
Oracle, I also Faced
E-Mail Answers

Answer Posted / vinay

Select distinct trans_id ,
to_char(trans_date,'MON') as TRANS_DATE,
count(trans_amt) over (partition by trans_id,to_char
(trans_date,'MON')) as No_OF_TRANSACTION
,sum(trans_amt) over (partition by trans_id,to_char
(trans_date,'MON')) as TRANS_AMOUNT,
debit_credit_indicator from CUSTOMER

Is This Answer Correct ?

3 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is trigger types of trigger?

1141

How to avoid using cursors? What to use instead of cursor and in what cases to do so?

1247

What is sharding in sql?

1039

What is auto increment in sql?

1175

How long will it take to learn pl sql?

979

What is the difference between clustered and non-clustered index in sql?

1065

What is trigger and stored procedure in sql?

1092

what are the different type of sql's statements ? : Sql dba

976

What's the procedure?

982

What are the different operators available in sql?

1057

How to revise and re-run the last sql command?

1218

how to implement one-to-one, one-to-many and many-to-many relationships while designing tables? : Sql dba

1056

How do I remove sql developer from windows 10?

1182

Explain spool.

1219

what is the difference between primary key and unique key? : Sql dba

1084