CODEWITHSUNDEEP
sqlsql-server-2008-r2
gruber
gruber

Reputation: 29759

sql sum up connection time in call detail records table

lets say Ive got Customers Detail Records table which has columns:

UserAId
UserBId
Duration
Impulses

For eample:

UserAId UserBId Duration Impulses
1       2       30       5
1       2       20       3
2       1       10       2
2       3       5        1

Ok, now I would like to write a query which would aggregate total Duration, Impulses and count of calls betweend users without direction so that the result would look like:

UserAId UserBId TotalDuration TotalImpulses TotalCallsCount
1       2       60            10            3
2       3       5             1             1

Is it possible ? If so then how to do this > thanks for help

Upvotes: 0

Views: 71

Answers (1)

Frazz
Frazz

Reputation: 3043

Of course, if you execute a query like this:

SELECT
  UserAId,
  UserBId,
  SUM(Duration) AS TotalDuration,
  SUM(Impulses) AS TotalImpulses,
  COUNT(*)      AS TotalCallsCount
FROM CustomerDetail
GROUP BY UserAId, UserBId

... you will not get what you want. That is because this query does not aggregate and combine the rows that have UserAId=1 and UserBId=2 with those that have UserAId=2 and UserBId=1.

To do what you want you need a little trick. What you call UserAId and UserBId in the result set are not actually always what you read on the input table. This query will do what you ask:

SELECT
  CASE WHEN UserAId<UserBId THEN UserAId ELSE UserBId END AS User_AId,
  CASE WHEN UserAId<UserBId THEN UserBId ELSE UserAId END AS User_BId,
  SUM(Duration) AS TotalDuration,
  SUM(Impulses) AS TotalImpulses,
  COUNT(*)      AS TotalCallsCount
FROM CustomerDetail
GROUP BY
  CASE WHEN UserAId<UserBId THEN UserAId ELSE UserBId END,
  CASE WHEN UserAId<UserBId THEN UserBId ELSE UserAId END

... it works even if UserAId=UserBId (you did not state if those two values can or cannot be the same). You will always get as User_AId the lesser of the 2 Ids, and as User_BId the greater of the 2 Ids... even if that combination does not exist as UserAId, UserBId nowhere in the table (obviously only if it does exist as UserBId, UserAId).

I have tested this on SQLFiddle here.

I am no SQL-Server expert. Some engines do allow the GROUP BY clause to reference calculated columns defined in the SELECT expression list without having to redefine them explicitly. This is non standard SQL, but it does make the SQL much more readable. Not sure if SQL-Server supports some sort of syntax for this.

Upvotes: 1

Related Questions