CODEWITHSUNDEEP
mysql
Kimomaru
Kimomaru

Reputation: 1023

Querying for a list of top entries and their values

I have a very basic table with a list of transactions (in this case, they're Call Detail Reporting records);

CREATE TABLE `cdr_records` (
  `dateTimeOrigination` int(11) DEFAULT NULL,
  `callingPartyNumber` varchar(50) DEFAULT NULL,
  `originalCalledPartyNumber` varchar(50) DEFAULT NULL,
  `finalCalledPartyNumber` varchar(50) DEFAULT NULL,
  `pkid` varchar(50) NOT NULL DEFAULT '',
  `duration` int(11) DEFAULT NULL,
  `destDeviceName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`pkid`)
)

When I query the transactions in this table, I get output like this;

from_unixtime(dateTimeOrigination) | callingPartyNumber | originalCalledPartyNumber | finalCalledPartyNumber | sec_to_time(duration) |
+------------------------------------+--------------------+---------------------------+------------------------+-----------------------+
| 2014-09-26 08:22:11                | 12345            | exampleNumber             | exampleNumber          | 02:49:54              |
| 2014-09-26 15:06:35                | 67891            | exampleNumber             | exampleNumber          | 02:39:46              |
| 2014-09-26 17:46:33                | 67891            | exampleNumber             | exampleNumber          | 02:37:13              |
| 2014-08-21 17:41:30                | 12345            | exampleNumber             | exampleNumber          | 02:23:55              |
| 2014-08-21 14:43:01                | 12345            | exampleNumber             | exampleNumber          | 02:01:56

I would like to write query that does two things;

1) Tells me who are the top talkers based on the duration of their calls 2) What the total duration was for all of that particular user's calls were for that period

How is something like this approached? If this is done with a DISTINCT query, how can I sum up the total number of duration values for each entry?

Upvotes: 0

Views: 50

Answers (1)

David162795
David162795

Reputation: 1866

SELECT callingPartyNumber, sec_to_time(duration_sum)
FROM
(
   SELECT callingPartyNumber, sum(duration) as duration_sum 
   FROM cdr_records
   WHERE dateTimeOrigination
      BETWEEN UNIX_TIMESTAMP('014-09-26 08:22:11')
          AND UNIX_TIMESTAMP('2014-08-21 14:43:01')
   GROUP BY callingPartyNumber
   ORDER BY duration_sum
) sub1

Upvotes: 1

Related Questions