X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 27261475DF2
	for <pgsql-sql@postgresql.org>; Fri, 31 Jan 2003 12:54:17 -0500 (EST)
Received: from blueyonder.co.uk (pcow053o.blueyonder.co.uk [195.188.53.96])
	by postgresql.org (Postfix) with ESMTP id 5195F475DC0
	for <pgsql-sql@postgresql.org>; Fri, 31 Jan 2003 12:54:16 -0500 (EST)
Received: from [10.0.1.2] ([80.195.42.16]) by blueyonder.co.uk with Microsoft
	SMTPSVC(5.5.1877.757.75); Fri, 31 Jan 2003 17:54:04 +0000
User-Agent: Microsoft-Entourage/10.1.1.2418
Date: Fri, 31 Jan 2003 17:55:49 +0000
Subject: Help with a query for charting
From: Andrew Veitch <andrew.veitch@blueyonder.co.uk>
To: <pgsql-sql@postgresql.org>
Message-ID: <BA606925.884E%andrew.veitch@blueyonder.co.uk>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/24
X-Sequence-Number: 11421

I'm trying to do a query to count the number of tickets opened on each day
of a month. It'll always be from the 1st to the end of the month. This does
it:

SELECT TO_CHAR(DATE_TRUNC('day',date_opened),'DD') AS day, COUNT(*)
    FROM ticket GROUP BY DATE_TRUNC('day', date_opened)
    WHERE <date range here>;

But it doesn't give me a zero for the days when no tickets were opened -
which I really need because I want to graph the result.

I could do this in the application code but that doesn't seem right.

Help would be great.

Andrew