calculating statistics with pure SQL queries

Question

Hi,

The SQL table consists of columns calling_party, called_party and call_duration. The example records are the following:

A,B,23
A,C,12
A,H,90
B,R,56
N,B,78

For every user that is stored in both roles as calling_party and called_party I would like to make statistics, for example what was the total duration of incoming conversations, what was the number of outgoing conversations etc.

To select users I wrote the following query:

SELECT DISTINCT t1.calling_party
  FROM dbo.monthly_connections AS t1
 INNER JOIN (SELECT called_party FROM dbo.monthly_connections) AS t2
       ON t1.calling_party = t2.called_party AS table_users

From here on I know how to create a method in a chosen programming language that goes through all returned rows and performs a guery for each row:

SELECT SUM(call_duration), COUNT(*)
  FROM table_users
 WHERE calling_party = current_user
 GROUP BY calling_party

If it is possible I would prefer to write a nested query that would calculate the statistics for every user without the help of custom method written in programming language but don't know how to do it. Does anyone know how to do it?

Answer 1

Something like that?

WITH INCOMING AS (
  SELECT called_party PARTY, SUM(call_duration) INCOMING_TOTAL
    FROM monthly_connections
   GROUP BY called_party
),
OUTGOING AS (
  SELECT calling_party PARTY, SUM(call_duration) OUTGOING_TOTAL
    FROM monthly_connections
   GROUP BY calling_party
)
SELECT COALESCE(INCOMING.PARTY,OUTGOING.PARTY) AS PARTY,
       INCOMING.INCOMING_TOTAL, OUTGOING.OUTGOING_TOTAL
  FROM INCOMING
  FULL OUTER JOIN OUTGOING ON OUTGOING.PARTY = INCOMING.PARTY