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.
- 1 Answers
- 4625 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
Can we use update in sql function?
what is the difference between group by and order by in sql? : Sql dba
How does a self join work?
How do I find duplicates in two columns?
How can we avoid duplicating records in a query?
what are the advantages of sql ? : Sql dba
How to write pl sql program in mysql command prompt?
How do I view a table in sql?
Is full outer join same as cross join?
What are all types of user defined functions?
How does rowid help in running a query faster?
Can a varchar be a primary key?
How to get each name only once from an employee table?
What is difference between joins and union?
What is the difference between explicit and implicit cursors in oracle?
