postgresqlpbx3cxpabx

Trying to write a query for 3CX outgoing call duration stats for the current day


So far I have managed to write a query to list all the outgoing calls made by a user and their duration, but what I'm looking for is, rather than a list of all the individual calls, a total of the duration.

SELECT starttime, duration, is_answ, is_fromoutside, from_no
  FROM callhistory3
 WHERE is_answ = 't'
   AND is_fromoutside = 'f'
   AND starttime >= CURRENT_DATE
   AND from_no = '101';

The duration is in the format 00:00:00:00.000 (Days:Hours:Minutes:Seconds.Hundredths), so my question is really how do I add this up and present it as a single number for duration, rather than a list of individual calls each with their own separate duration?

My final query is as follows to anyone that is interested:

SELECT from_no, SUM(duration), COUNT (*)
  FROM callhistory3
 WHERE is_answ = 't'
   AND is_fromoutside = 'f'
   AND starttime >= CURRENT_DATE
 GROUP BY from_no
 ORDER BY from_no;

Solution

  • If the duration really always has exactly that format:

    SELECT from_no, sum(duration)
      FROM callhistory3
      WHERE is_answ = 't'
        AND is_fromoutside = 'f'
        AND starttime >= CURRENT_DATE
        AND from_no = '101'
      GROUP BY from_no
      ORDER BY from_no;
    

    Well, if you really are just doing it for one from_no you could simplify it by just selecting the sum() and leaving off the GROUP BY and ORDER BY clauses, but it seems likely you might want a list; for that, you can just change the WHERE clause above.