From erouse@comsquared.com Thu May  24 19:15:32 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id A461F9FB936
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; Thu, 24 May 2007 14:49:49 -0400 (EDT)
From: "Edward W. Rouse" <erouse@comsquared.com>
To: <pgsql-sql@postgresql.org>
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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2800.1528" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D410204418-24052007>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 </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D410204418-24052007>select =

count(distinct(id)) from audit where timestamp &gt;=3D=20
'01-may-2007'</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D410204418-24052007>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</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007>date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

count</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007>01-may-2007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
107</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007>02-may-2007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
215</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007>03-may-2007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
96</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007>04-may-2007&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;=20
0</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D410204418-24052007>I =
would prefer the 0=20
entries be included but can live without them. =
Thanks.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D410204418-24052007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D410204418-24052007>Oh, =
postgres 7.4 by=20
the way.</SPAN></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV align=3Dleft><SPAN style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">
<P class=3DMsoPlainText style=3D"MARGIN: 0in 0in 0pt" align=3Dleft><FONT =

face=3D"Courier New">Edward W. Rouse<?xml:namespace prefix =3D o ns =3D=20
"urn:schemas-microsoft-com:office:office" /><o:p></o:p></FONT></P>
<P class=3DMsoPlainText style=3D"MARGIN: 0in 0in 0pt"><FONT=20
face=3D"Courier New">ComSquared Systems, Inc.<o:p></o:p></FONT></P>
<P class=3DMsoNormal></SPAN><SPAN=20
style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">770-734-5301</SPAN></P>
<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN=20
style=3D"FONT-SIZE: 12pt"></SPAN></FONT>&nbsp;</P></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

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




From andreas.kretschmer@schollglas.com Thu May  24 19:42:49 2007
Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B12FD9FBFC1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 16:42:49 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 92112-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 16:42:46 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by postgresql.org (Postfix) with ESMTP id CEECF9FB92B
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2007 16:42:45 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu1) with ESMTP (Nemesis),
	id 0MKwpI-1HrJCt2kdy-00041n; Thu, 24 May 2007 21:42:44 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HrJCs-0005Vi-00
	for pgsql-sql@postgresql.org; Thu, 24 May 2007 21:42:42 +0200
Date: Thu, 24 May 2007 21:42:42 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: group by day
Message-ID: <20070524194242.GA21054@a-kretschmer.de>
References: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1+qcVgQWCkDYdXEJUazOA6dTKjcBAsXAUu22If
	eibYkfOLrZKLQqHcyqQhoRQdGRjtRdxNjNDj8NzPACz/SlUbt/
	qHDy4oPZvhR/yysO9NPuKUozvHQrSWk
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/176
X-Sequence-Number: 28456

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 |  10
 2007-05-01 08:00:00 |  20
 2007-05-02 10:00:00 |  20
 2007-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 |  50
 2007-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



From rdeleonp@gmail.com Thu May  24 19:46:12 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 3ABFF9FC036
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 16:46:12 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 28549-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 16:46:02 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.246])
	by postgresql.org (Postfix) with ESMTP id 7B2FB9FC047
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2007 16:46:01 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so106563ana
	for <pgsql-sql@postgresql.org>; Thu, 24 May 2007 12:45:48 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=AvW99vrUaeK9HGl/vBiQ5hS1KFdH1MrvzUw/kFFvtv+fLE9pkapGJmsZb3HQJ/uYVZ3AwaEu7SG8u5/+XGC9pQCxF3Oh2g6h04WhT/6nnPT7kB9tnJ1L/Nqz8d2Ennq1uwraj5inTnVV8e58BcrpqgFnU75/q2FtFowLHX1qDv4=
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=
Received: by 10.100.173.19 with SMTP id v19mr2072845ane.1180035947602;
	Thu, 24 May 2007 12:45:47 -0700 (PDT)
Received: by 10.100.154.9 with HTTP; Thu, 24 May 2007 12:45:47 -0700 (PDT)
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>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/177
X-Sequence-Number: 28457

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



From dev@archonet.com Fri May  25 08:08:55 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 169829FB84E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 25 May 2007 05:08:55 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 73115-03-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 25 May 2007 05:08:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 8DDAC9FB63A
	for <pgsql-sql@postgresql.org>; Fri, 25 May 2007 05:08:44 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 8C11776BF8;
	Fri, 25 May 2007 09:08:34 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id AA0452740EB;
	Fri, 25 May 2007 09:08:33 +0100 (BST)
Message-ID: <4656997F.2040400@archonet.com>
Date: Fri, 25 May 2007 09:08:31 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: "Edward W. Rouse" <erouse@comsquared.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: group by day
References: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>
In-Reply-To: <04c201c79e34$4e3877c0$143c520a@ntc2s.comsquared.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/178
X-Sequence-Number: 28458

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