Finding Count of Outgoing and Incoming calls from a Caller Log table in Oracle has not covered total scenario and the query is not universal. Let us suppose the callerlog table with below data Code: CALLERID CALLER CALLEDTO CALLER_DT OUTGOING_SEC 1 123 456 02/21/2013 30 2 123 456 02/21/2013 10 3 456 123 02/21/2013 10 4 123 678 02/21/2013 20 5 678 456 02/21/2013 40 6 456 678 02/20/2013 20 7 789 555 02/21/2013 10 8 567 321 02/20/2013 100 Here we have included caller_dt and outgoing_sec to cover total scenario. The required output should be as below Code: CALLER OUTGOING INCOMING OUTGOING_SEC INCOMING_SEC CALLER_DT 123 3 1 60 10 02/21/2013 321 0 1 0 100 02/21/2013 456 1 0 20 0 02/20/2013 456 1 3 10 80 02/21/2013 555 0 1 0 10 02/21/2013 567 1 0 100 0 02/21/2013 678 0 1 0 20 02/20/2013 678 1 1 40 20 02/21/2013 789 1 0 10 0 02/21/2013 If we take an example of callerid 7 and 8 we can observe that 789 has only outgoing not incoing for a particular date and in similar way 321 has only incoing not outgoing.So in output we will be having 0 incoming for 789 and 0 incoming seconds. For 321 we will be having 0 outgoing and 0 outgoing seconds. In previous article we have used below query Code: select A.caller,A.outgoing,B.incoming from (select caller ,count(*) outgoing from callerlog group by caller) A , (select calledto,count(*) incoming from callerlog group by calledto) B where A.caller=B.calledto The above query will work for the scenario which is mentioned in the article but for the above scenario it will fails. Because it has a joining condition on caller and calledto. In the above scenario we have seen the caller's who has only outgoing and also caller's who has only incoming calls. Below query will work universally for the above scenario Code: select caller,outgoing,incoming,outgoing_sec,incoming_sec,caller_dt from ( select caller,sum(outgoing) over (partition by caller,caller_dt order by caller_dt) outgoing , sum(incoming) over (partition by caller,caller_dt order by caller_dt) incoming, sum(outgoing_sec) over (partition by caller,caller_dt order by caller_dt) outgoing_sec, sum(incoming_sec) over (partition by caller,caller_dt order by caller_dt) incoming_sec, caller_dt, row_number() over (partition by caller,caller_dt order by caller_dt) rnk from ( select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec from callerlog group by caller,caller_dt union select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt )A ) where rnk=1 order by caller,caller_dt Now let us analyze the query part by part. The below query Code: select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec from callerlog group by caller,caller_dt The above query will give the output of only the information of outgoing output will be as below Code: CALLER OUTGOING INCOMING CALLER_DT OUTGOING_SEC INCOMING_SEC 123 3 0 02/21/2013 60 0 456 1 0 02/20/2013 20 0 456 1 0 02/21/2013 10 0 567 1 0 02/21/2013 100 0 678 1 0 02/21/2013 40 0 789 1 0 02/21/2013 10 0 The below query Code: select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt The above query will give the output of only the information of outgoing Output will be as below Code: CALLEDTO OUTGOING INCOMING CALLER_DT OUTGOING_SEC INCOMING_SEC 123 0 1 02/21/2013 0 10 321 0 1 02/21/2013 0 100 456 0 3 02/21/2013 0 80 555 0 1 02/21/2013 0 10 678 0 1 02/20/2013 0 20 678 0 1 02/21/2013 0 20 Instead of using join condition as of previous query we are using union to club all the records. The below union query will combine the records from two queries output Code: select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec from callerlog group by caller,caller_dt union select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt Output will be as below Code: CALLER OUTGOING INCOMING CALLER_DT OUTGOING_SEC INCOMING_SEC 123 0 1 02/21/2013 0 10 123 3 0 02/21/2013 60 0 321 0 1 02/21/2013 0 100 456 0 3 02/21/2013 0 80 456 1 0 02/20/2013 20 0 456 1 0 02/21/2013 10 0 555 0 1 02/21/2013 0 10 567 1 0 02/21/2013 100 0 678 0 1 02/20/2013 0 20 678 0 1 02/21/2013 0 20 678 1 0 02/21/2013 40 0 789 1 0 02/21/2013 10 0 Now we need to roll-up the data by using sum function as in below query Code: select caller,sum(outgoing) over (partition by caller,caller_dt order by caller_dt) outgoing , sum(incoming) over (partition by caller,caller_dt order by caller_dt) incoming, sum(outgoing_sec) over (partition by caller,caller_dt order by caller_dt) outgoing_sec, sum(incoming_sec) over (partition by caller,caller_dt order by caller_dt) incoming_sec, caller_dt, row_number() over (partition by caller,caller_dt order by caller_dt) rnk from ( select caller ,count(*) outgoing, 0 as incoming,caller_dt,sum(outgoing_sec) outgoing_sec ,0 as incoming_sec from callerlog group by caller,caller_dt union select calledto,0 as outgoing,count(*) incoming,caller_dt , 0 as outgoing_sec,sum(outgoing_sec) incoming_sec from callerlog group by calledto,caller_dt )A Output will be as below Code: CALLER OUTGOING INCOMING OUTGOING_SEC INCOMING_SEC CALLER_DT RNK 123 3 1 60 10 02/21/2013 1 123 3 1 60 10 02/21/2013 2 321 0 1 0 100 02/21/2013 1 456 1 0 20 0 02/20/2013 1 456 1 3 10 80 02/21/2013 1 456 1 3 10 80 02/21/2013 2 555 0 1 0 10 02/21/2013 1 567 1 0 100 0 02/21/2013 1 678 0 1 0 20 02/20/2013 1 678 1 1 40 20 02/21/2013 1 678 1 1 40 20 02/21/2013 2 789 1 0 10 0 02/21/2013 1 In the above output we can see the duplicate records. To filter it out we have used row_number() function. If we filter the duplicate records based on rnk column , we will be getting desired output.