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/