Calculating statistics with pure SQL queries

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 query 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?

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