Querying counts by date range

Databases
1

Nothing super major here. I have a query that I run multiple times against our ticket system to figure the average number of tickets per month. Right now I just have the query in the middle of a PHP loop to run it for several months and it works. But I’m wondering if there’s a way to do it in just a single query. Here’s what I have now:

SELECT
    COUNT(os_ticket.ticket_id)
FROM
    ticketsys
WHERE
    ticketsys.created BETWEEN '2010-08-01' AND '2010-08-31'

Is there a way to make that loop to grab, say, the last 3 months? Or maybe pass it the last 3 months from the PHP script. I’m just wanting to get it down to one query rather than the current 3.

Thanks.

2

Thus, my post. :slight_smile: I’m going back through a few of my easier reports looking for ways to optimize them so that I can learn enough to tackle a huge Oracle report I have that takes 10 minutes to run.

I’m probably being dense here, but I’m not seeing how to do the grouping. I don’t have a month field. I tried to Group By the created field (which is a timestamp) but that didn’t work.

Would you mind throwing me some pseudo-code?

3

Doh! I should have saw that.

This query is for a db on MySQL so no worries on it. I’ll probably be posting again shortly when I go to work on the Oracle ones.

Thanks for the help.

4

You can use DATE_ADD or DATE_SUB to specify 1 month ago or ahead or 2 or more months for example.

adding a GROUP BY clause and grouping on MONTH would give you the counts for each of the months in your query.

Any time you find yourself running a query in a loop realize there is more you could be doing with the query to make it more efficient.

5

Get count by month for all of 2009 for example:


SELECT
MONTH(ticketsys.created) as WhichMonth,
    COUNT(os_ticket.ticket_id) as Total
FROM
    ticketsys
WHERE
    ticketsys.created BETWEEN '2009-01-01' AND '2009-12-31'
GROUP BY MONTH(ticketsys.created)

Note that if you are trying to use mysql syntax to run an oracle report you could run into problems because there are differences between the two databases.