SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Location
    San Antonio, TX
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    querying counts by date range

    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:

    Code:
    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. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Location
    San Antonio, TX
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by guelphdad View Post
    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.
    Thus, my post. 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.

    Quote Originally Posted by guelphdad View Post
    adding a GROUP BY clause and grouping on MONTH would give you the counts for each of the months in your query.
    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?

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get count by month for all of 2009 for example:

    Code:
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Location
    San Antonio, TX
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doh! I should have saw that.

    Quote Originally Posted by guelphdad View Post
    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.
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •