Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: MySQL: Grouping date by quarterly and half yearly

    Hello

    I am looking for some mysql query assistance from you experts


    I have a table that stores all the download logs of software on my website.

    The table structure is as below:

    Code:
    CREATE TABLE `software_downloads` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `software_id` INT(10) DEFAULT NULL,
      `download_date` DATETIME DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1
    So what I am looking for is, when I generate reports, my customers should be able to group the dates by quarterly and half yearly. That means, if they choose the quarterly option, the report should list all the count of software download logs and group them by three months and six months in case if they choose the half-yearly option.


    Thanks in advance for your help.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It depends on how you determine the quarter, do you mean Jan-Mar, Apr-Jun, July-Sep, Oct-Dec or from this point in time 3 months back, 6 months back etc?

    If you look at FLOOR((DATE_FORMAT(download_date,'%m')-1)/3) will give you the quarter in which the download took place.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DATE_FORMAT(download_date,'%m') produces a string

    use MONTH(download_date) instead, which produces a number, much more amenable to your numeric calculation

    prettier, too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2008
    Posts
    36
    Quote Originally Posted by it-iss.com View Post
    It depends on how you determine the quarter, do you mean Jan-Mar, Apr-Jun, July-Sep, Oct-Dec or from this point in time 3 months back, 6 months back etc?

    If you look at FLOOR((DATE_FORMAT(download_date,'%m')-1)/3) will give you the quarter in which the download took place.
    Yes sir, I mean Jan-Mar, Apr-Jun, July-Sep, Oct-Dec, so would FLOOR((DATE_FORMAT(download_date,'%m')-1)/3) do this for me?

    Thanks

  5. #5
    Join Date
    Mar 2008
    Posts
    36
    One more query.

    Please consider the following DDL

    Code:
    CREATE TABLE `software_downloads` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `software_id` INT(10) DEFAULT NULL,
      `download_by` VARCHAR(10) NOT NULL,
      `download_date` DATETIME DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1

    Here I added a new field "download_by", The values for this column will either be "admin" or "customer". so will it be possible to find out how many of the downloads are made by admin and how many of them are made by customers withing the same query?


    Thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    theres two elements to this
    one element is the marshalling of the data, the query. it-iss.com's solution should work ok for that
    ..you may need two versions of this query
    eg

    FLOOR((MONTH(download_date,-1)/3) for quarters
    FLOOR((MONTH(download_date,-1)/6) for half year

    the other part is the actual report, but you don't mention what you are using as the front end, the user interface. you should be able to do the selection between monthly / quarterly / whatever time period you require.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2008
    Posts
    36
    Hi healdem

    Thanks for your reply. Do you have a solution to my last post here ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cancer10 View Post
    Do you have a solution to my last post here ?
    yeah, use GROUP BY download_by
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2008
    Posts
    36
    Thank you very much for your kind help.

Posting Permissions

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