CODEWITHSUNDEEP
sql
Niko Gamulin
Niko Gamulin

Reputation: 66555

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?

Upvotes: 2

Views: 1041

Answers (1)

Johann Blais
Johann Blais

Reputation: 9469

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

Upvotes: 1

Related Questions