DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=IZnbW8Zac+ZS9/89RiYBr3sIhofXAB8ifOuhu8E52he4WWjcVzQ6Hqb17vsU3mmlEVSyzSgWyAV6wFDYmoGf3nbSgupO1Z/GEfEBPfPDSD5RS/Bbo/mT2tn8voastqfsg7g0RgpOX6oDIfq9ji+241/NuxWzebHL4pMwOFG3jmE=
Message-ID: <a55915760705241245q521967e1rfc46d7639ebb8bc2@mail.gmail.com>
Date: Thu, 24 May 2007 14:45:47 -0500
From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org, "Edward W. Rouse" <erouse@comsquared.com>
Subject: Re: group by day
In-Reply-To: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>

On 5/24/07, Edward W. Rouse <erouse@comsquared.com> wrote:
>
>
> I have an audit table that I am trying to get a count of the number of
> distinct entries per day by the external table key field. I can do a
>
> select count(distinct(id)) from audit where timestamp >= '01-may-2007'
>
> and get a total count. What I need is a way to group on each day and get a
> count per day such that the result would be something like
>
> date                    count
> 01-may-2007        107
> 02-may-2007        215
> 03-may-2007        96
> 04-may-2007        0
>
>
> I would prefer the 0 entries be included but can live without them. Thanks.
>
> Oh, postgres 7.4 by the way.
>
>
>
> Edward W. Rouse
>
> ComSquared Systems, Inc.
>
> 770-734-5301

SELECT   TIMESTAMP, COUNT(DISTINCT(ID))
    FROM AUDIT
GROUP BY TIMESTAMP
ORDER BY TIMESTAMP