I have a transaction table called TRANSACTION DETAIL....
TRANSACTION DETAIL
CNN TYPE AMT DATE
C1 C 1000 10-Jan-16
C2 C 1200 10-Jan-16
C3 C 2000 11-Jan-16
C4 D 1000 12-Jan-16
C3 D 2000 12-Jan-16
C2 C 1100 12-Jan-16
I need total credit and debit for those 3 paricular dates.
REQUIRED OUTPUT
DATE TOTAL DEBIT TOTAL CREDIT
10-Jan-16 0 2200
11-Jan-16 2000 0
12-Jan-16 3000 100
Please help me out on this to get the query...
Thanks,
Sam
You're looking for a "group by then pivot". Calculate the sum for each date and type:
create table t (
cnn varchar2(2), type varchar2(1), amt integer, dt date
);
insert into t values ('C1','C', 1000, '10-Jan-16');
insert into t values ('C2','C', 1200, '10-Jan-16');
insert into t values ('C3','C', 2000, '11-Jan-16');
insert into t values ('C4','D', 1000, '12-Jan-16');
insert into t values ('C3','D', 2000, '12-Jan-16');
insert into t values ('C2','C', 1100, '12-Jan-16');
commit;
select dt, type, sum(amt) sm from t
group by dt, type;
DT T SM
-------------------- - ----------
10-JAN-0016 00:00:00 C 2200
12-JAN-0016 00:00:00 D 3000
11-JAN-0016 00:00:00 C 2000
12-JAN-0016 00:00:00 C 1100Then pivot the results of this to get credit and debit as columns. As you're on 10g, you'll have to do this the long way:
select dt,
nvl(max(case when type = 'C' then sm end), 0) credit_total,
nvl(max(case when type = 'D' then sm end), 0) debit_total
from (
select dt, type, sum(amt) sm from t
group by dt, type
)
group by dt
order by 1;
DT CREDIT_TOTAL DEBIT_TOTAL
-------------------- ------------ -----------
10-JAN-0016 00:00:00 2200 0
11-JAN-0016 00:00:00 2000 0
12-JAN-0016 00:00:00 1100 3000On 11g you can do this instead:
select * from (
select dt, type, sum(amt) sm from t
group by dt, type
)
pivot (
sum(sm) as total for (type) in ('C' as credit, 'D' as debit)
);
DT CREDIT_TOTAL DEBIT_TOTAL
-------------------- ------------ -----------
10-JAN-0016 00:00:00 2200
11-JAN-0016 00:00:00 2000
12-JAN-0016 00:00:00 1100 3000