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
5302 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 aggregate function in sql?

1010

What is the difference between function and procedure in pl/sql?

1042

What is user in sql?

992

what is auto increment? : Sql dba

982

What are the constraints available in sql?

1017

What is the difference between syntax error and runtime error?

1093

Compare sql & pl/sql

1079

what is 'mysqlimport'? : Sql dba

1000

What operating systems are supported by oracle sql developer?

922

Does oracle roll back the transaction on an error?

982

What is package in pl sql with an examples?

998

What is a delimiter in sas?

1010

Is sql a backend language?

1063

What is trigger in pl sql with examples?

1003

Enlist the characteristics of pl/sql?

1766