Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle

In day to day life we all use mobile phones and make outgoing and incoming calls. Normally our each call will be stored in the caller log in telecommunication tables.

We will call to call center to enquire about the no. of incoming calls and outgoing calls we have done. They will enter our no. in the portal and backend it will query it caller log table and it will provide the results.

Now we will see how it works by looking into below scenario.

Let us suppose we have a caller_log table and data as below

Code:

CALLERID CALLER	CALLEDTO

1	123	456
2	123	456
3	456	123
4	123	678
5	678	456

Here 123,456 and 678 are mobile no.

If we observe the caller_log table we will find that no. of outgoing from mobile no. 123 is 3 (two times called to 456 and one time to 678) and no. of incoming is 1 (456 called one time to 123).

We need output as below

Code:

CALLER	OUTGOING INCOMING

123	3	 1
456	1	 3
678	1	 1

Below query will give the required output

Code:

select A.caller,A.outgoing,B.incoming from 
(select caller ,count(*) outgoing from pbatch.caller group by caller) A ,
(select calledto,count(*) incoming from pbatch.caller group by calledto) B
where A.caller=B.calledto

The inner query

Code:

select caller ,count(*) outgoing from pbatch.caller group by caller

will give below result

Code:

CALLER	OUTGOING

123	3
456	1
678	1

The inner query

Code:

select calledto,count(*) incoming from pbatch.caller group by calledto

will give below result

Code:

CALLEDTO INCOMING

123	 1
456	 3
678	 1

We will treat these two inner queries as two table and join based on caller and calledto which will give the required output.


If we add one more date column then we can roll-up the date based on date.

If we add two more columns incoming time in sec and outgoing time in sec then we will have complete table of caller_log. Now we can write query for total no. of incoming and outgoing calls with total no. of outgoing sec and incoming sec on a particular date.


We can use below query to achieve above output by using connect by prior clause

Code:

select A.caller,A.outgoing,B.incoming from
(select caller,count(*) outgoing from 
 (select caller,calledto,level lvl from pbatch.caller  connect by NOCYCLE prior calledto=caller)A where A.lvl=1 group by caller)A,
(select calledto,count(*) incoming from 
 (select caller,calledto,level lvl from pbatch.caller  connect by NOCYCLE prior calledto=caller)A where A.lvl=1 group by calledto)B
where A.caller=B.calledto