Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A461F9FB936 for ; Thu, 24 May 2007 16:15:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 70374-07 for ; Thu, 24 May 2007 16:15:17 -0300 (ADT) X-Greylist: delayed 00:25:08.055035 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id D0FE89FB922 for ; Thu, 24 May 2007 16:15:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mdpserv.comsquared.com (190.milner.com [199.72.227.190]) by svr4.postgresql.org (Postfix) with ESMTP id 624B95AF04F for ; Thu, 24 May 2007 18:50:03 +0000 (GMT) Received: from EROUSE (erouse.ntc2s.comsquared.com [10.82.60.20] (may be forged)) by mdpserv.comsquared.com (8.13.6+Sun/8.13.6) with ESMTP id l4OInmVF007180 for ; Thu, 24 May 2007 14:49:49 -0400 (EDT) From: "Edward W. Rouse" To: Subject: group by day Date: Thu, 24 May 2007 14:49:47 -0400 Message-ID: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_04C3_01C79E12.C726D7C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.187 tagged_above=0 required=5 tests=AWL=0.050, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200705/175 X-Sequence-Number: 28455 This is a multi-part message in MIME format. ------=_NextPart_000_04C3_01C79E12.C726D7C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_04C3_01C79E12.C726D7C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
I have = an audit=20 table that I am trying to get a count of the number of distinct entries = per day=20 by the external table key field. I can do a
 
select = count(distinct(id)) from audit where timestamp >=3D=20 '01-may-2007'
 
and = get a total=20 count. What I need is a way to group on each day and get a count per day = such=20 that the result would be something like
 
date       =              = count
01-may-2007      = ; =20 107
02-may-2007      = ; =20 215
03-may-2007      = ; =20 96
04-may-2007      = ; =20 0
 
 
I = would prefer the 0=20 entries be included but can live without them. = Thanks.
 
Oh, = postgres 7.4 by=20 the way.
 

Edward W. Rouse

ComSquared Systems, Inc.

770-734-5301

 

 
------=_NextPart_000_04C3_01C79E12.C726D7C0--