Re: Help with a query for charting

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> 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.
> ...
> But it doesn't give me a zero for the days when no tickets were opened

The problem is that SQL has no concept of how many days there are supposed
to be inside of the range you gave it, now does it have a way of easily
determining how many months are in a year. You will have to put that
information into the database: a simple table with a date field and one
row per day should do it. Make sure that you go well beyond any days
you will ever need. For example:

CREATE TABLE mydates (date_opened date);

(Using the same column name allows us to use "USING" instead of "ON"
in our JOIN later on.)

Populate it somehow. Here is a quick and dirty way to add 1000 days:

perl -e \
"{print \"INSERT INTO mydates VALUES('\" . scalar
localtime($^T+(\$x*86400)). \"');\n\"; redo if \$x++<1000}" \
| psql

Watch those escape characters!

Once you have such a table, you will need to join your query to it,
by using a RIGHT OUTER JOIN (RIGHT OUTER as we are listing the
important table first, then making sure that we have at least one
row from the second, or "right" table). We also need to wrap the
query for the first table inside of a subselect to allow us to use
the GROUP BY with a JOIN. The date specification is only needed on
the second table (mydates), although you could add it to the first
as well if you wish. The TO_CHAR has been moved to the "outer level",
so we can simply join on the DATE_TRUNC'ed column. Finally, a COALESCE
on the count is added, in order to generate the wanted zeroes:

SELECT TO_CHAR(DATE_TRUNC('day',T2.date_opened), 'DD') AS "day",
COALESCE(T1.mycount,0) AS "count"
FROM
(SELECT date_opened, COUNT(*) AS mycount
FROM ticket GROUP BY date_opened) AS T1
RIGHT OUTER JOIN
(SELECT DISTINCT date_opened
FROM mydates
WHERE date_opened BETWEEN '23-Jan-2003' AND '26-Jan-2003') AS T2
USING (date_opened)
ORDER BY "day" ASC;

The DISTINCT is not strictly needed, but is a safeguard in case the
mydates table has more than one entry with the same date.

Hope that helps.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302021403

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+PX6rvJuQZxSWSsgRAqAxAKC/NwhBKTavlNXYkTmsy7DMcxeLPwCgnP4K
y2RTdNiyQv+V29prKmo1yMw=
=bBpJ
-----END PGP SIGNATURE-----