Skip site navigation (1) Skip section navigation (2)

Re: Problems with query using aggregate functions

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: djzanky(at)gmail(dot)com,PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Problems with query using aggregate functions
Date: 2005-10-17 11:57:16
Message-ID: 4353919C.6050803@ethereal-realms.org (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Oliver ElphickDate: 2005-10-17 13:03:55
Subject: Re: Switching from MySQL -- Missing DESCRIBE table, SHOW
Previous:From: John J. UrbaniakDate: 2005-10-17 11:49:11
Subject: Re: Missing file LIBC06P1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group