Re: group by day

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