Thread: group by day
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
am Thu, dem 24.05.2007, um 14:49:47 -0400 mailte Edward W. Rouse folgendes: > 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. You are searching for GROUP BY. A simple example: test=*# select * from foo; ts | val ---------------------+-----2007-05-01 08:00:00 | 102007-05-01 08:00:00 | 202007-05-02 10:00:00 | 202007-05-02 11:00:00| 30 (4 rows) Time: 1.079 ms test=*# select ts::date, sum(val) from foo group by 1; ts | sum ------------+-----2007-05-02 | 502007-05-01 | 30 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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
Edward W. Rouse wrote: > > Oh, postgres 7.4 by the way. The latest release there is 7.4.17 - make sure you're running that revision. See the website for what bugs have been fixed. -- Richard Huxton Archonet Ltd