X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3E97D9DC97D for ; Thu, 16 Mar 2006 02:45:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95678-09 for ; Thu, 16 Mar 2006 02:45:38 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id F04D79DC86F for ; Thu, 16 Mar 2006 02:45:36 -0400 (AST) Received: from 208091.vserver.de (nirs.de [62.75.208.91]) by svr4.postgresql.org (Postfix) with ESMTP id BABC35AF9AB for ; Thu, 16 Mar 2006 06:45:36 +0000 (GMT) Received: from becker.local (p54AD3393.dip0.t-ipconnect.de [84.173.51.147]) (authenticated bits=0) by 208091.vserver.de (8.12.8/8.12.8) with ESMTP id k2G6jFih032355 for ; Thu, 16 Mar 2006 07:45:15 +0100 From: Stefan Becker To: pgsql-sql@postgresql.org Subject: Re: group by function, make SQL cleaner? Date: Thu, 16 Mar 2006 07:46:27 +0100 User-Agent: KMail/1.6.2 References: <4418F52F.4070804@obviously.com> In-Reply-To: <4418F52F.4070804@obviously.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200603160746.27272.stefan@yukonho.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.181 required=5 tests=[AWL=0.181] X-Spam-Score: 0.181 X-Spam-Level: X-Archive-Number: 200603/267 X-Sequence-Number: 24613 this should work,=20 # SELECT date_trunc('day',endtime),count(*) FROM eg_event where endtime >=3D '2006-02-01' and endtime < '2006-03-01' GROUP BY 1 ORDER BY 1; best regards, Stefan Am Donnerstag, 16. M=E4rz 2006 06:18 schrieb Bryce Nesbitt: > I've got a working query: > > stage=3D# SELECT date_trunc('day',endtime),count(*) > FROM eg_event where endtime >=3D '2006-02-01' and endtime < '2006-03-01' > GROUP BY date_trunc('day',endtime) > ORDER BY date_trunc('day',endtime); > > date_trunc | count > ---------------------+------- > 2006-02-01 00:00:00 | 253 > 2006-02-02 00:00:00 | 245 > 2006-02-03 00:00:00 | 231 > 2006-02-04 00:00:00 | 313 > 2006-02-05 00:00:00 | 285 > 2006-02-06 00:00:00 | 194 > 2006-02-07 00:00:00 | 229 > 2006-02-08 00:00:00 | 239 > 2006-02-09 00:00:00 | 250 > 2006-02-10 00:00:00 | 245 > 2006-02-11 00:00:00 | 275 > > Is there a way to eliminate the ugly repeated use of > date_trunc('day',endtime)? > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly =2D-=20 email: stefan@yukonho.de tel : +49 (0)6232-497631 http://www.yukonho.de