Re: Help with a query for charting

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/