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
5357 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 the difference between unique and primary key constraints?

1079

How to test for null values?

1056

What is the use of prepared statement?

1029

Explain the rollback statement?

1016

What are the most important characteristics of pl/sql?

1165

Why having clause is used in sql?

984

what is myisam? : Sql dba

1090

Explain scalar functions in sql?

1074

Does db2 use sql?

954

How can we implement rollback or commit statement in a trigger?

1041

What is the location of pre_defined_functions.

1166

What is rownum and rowid?

1038

what is the difference between join and union? : Sql dba

1122

What are stored procedures used for?

1004

What is %rowtype in pl sql?

994