What would be the best way to do this?

[gte806e]

I currently have a DB that contains action stats for a particular application. Right now it reports daily totals and totals over a range of dates as a sum.

My client now wants to display these totals in a graph format that can display both daily stats per statistic, as well as hourly per statistic.

I'm trying to think of the best way to pull all of this data out in one database call so I dont beat the hell out of it.

This is the code i currently have to pull daily "live" totals

SELECT actions.action_caption,
COUNT(archive_ddd.actionID) AS total
FROM actions RIGHT OUTER JOIN archive_ddd
ON actions.ID = archive_ddd.actionID
WHERE actions.action_category = '1'
AND actions.summary_include = '1'
GROUP BY actions.actionID

and for the date range i just add a date comparision in the where clause.

Now I tried added an additional group by to for DAYOFYEAR(archive_ddd.entry_time) but this just places the sum of ALL the actions for that day.

Any ideas what would be the best way to do this?

Thanks,