Thread: Help with a query for charting

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

you could keep a table with list of all possible days in a month.

left join that to the results you get from query below this will return NULL
for days where there is no data. NULL could then easily converted to 0 using CASE
or COALESCE.


regds
mallah.

> 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
>
>
> ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9'
> the postmaster



-----------------------------------------
Get your free web based email at trade-india.com.  "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/

-----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@turnstep.com
PGP Key: 0x14964AC8 200302021403

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

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