Re: Problems with query using aggregate functions

djzanky(at)gmail(dot)com wrote:
> Dear all,
>
> I have a table created with this specifications:
>
> CREATE TABLE cdr (
> calldate timestamp with time zone NOT NULL default now(),
> clid varchar(80) NOT NULL default '',
> src varchar(80) NOT NULL default '',
> dst varchar(80) NOT NULL default '',
> dcontext varchar(80) NOT NULL default '',
> channel varchar(80) NOT NULL default '',
> dstchannel varchar(80) NOT NULL default '',
> lastapp varchar(80) NOT NULL default '',
> lastdata varchar(80) NOT NULL default '',
> duration bigint NOT NULL default '0',
> billsec bigint NOT NULL default '0',
> disposition varchar(45) NOT NULL default '',
> amaflags bigint NOT NULL default '0',
> accountcode varchar(20) NOT NULL default '',
> uniqueid varchar(32) NOT NULL default '',
> userfield varchar(255) NOT NULL default ''
> );
>
> I want to extract the number of calls placed in 1 hour and the average
> call duration
>
> I'm working with this query:
>
> SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
> ORDER BY calldate;
>
> i tried several other queries but i'm not able to count the number of
> calls in an hour (better in a time interval) and calculate the average
> duration.
>
> Any suggestions would be appreciated! Tnx
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

Have you tried something like this?

SELECT DATE_TRUNC('hour', calldate) AS Hour
COUNT(duration) AS Calls
AVG(duration) AS Average
SUM(duration) AS Total
FROM Cdr
WHERE Src=601
GROUP BY Hour

Alternatively, you can try to use a HAVING clause to restrict based on
specific aggregate clauses such as calculated hours.

Martin Foster
martin(at)ethereal-realms(dot)org