Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts

    Question 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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Arithmetic.

    Code:
    SELECT YEAR(download_date) AS theYear,
           FLOOR( ( MONTH(download_date) - 1 ) / 3 ) AS theQuarter,
           COUNT(*) AS howMany
    FROM software_downloads
    GROUP BY YEAR(download_date), FLOOR( ( MONTH(download_date) - 1 ) / 3 )
    ORDER BY theYear, theQuarter
    and then

    Code:
    SELECT YEAR(download_date) AS theYear,
           FLOOR( ( MONTH(download_date) - 1 ) / 6 ) AS theHalf,
           COUNT(*) AS howMany
    FROM software_downloads
    GROUP BY YEAR(download_date), FLOOR( ( MONTH(download_date) - 1 ) / 6 )
    ORDER BY theYear, theHalf
    Here, theQuarter and theHalf will have values of 0,1,2,3 and 0,1. Add 1 if you need them to be 1,2,3,4 and 1,2.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-19-2011)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    I actually want the date to group by Jan-Mar, Apr-Jun, July-Sep, Oct-Dec.

    What modifications do I have to make in ur query to achieve this?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    ??? It *IS* doing that.

    Q0 is Jan to Mar
    Q1 is Apr to Jun
    Q2 is Jul to Sep
    Q3 is Oct to Dec

    You mean you want the query to return *TEXT* that says that???

    Code:
    CREATE TABLE quarters ( qnum INT, qname VARCHAR(20) );
    
    INSERT INTO quarters (qnum,qname)
    VALUES( 0, 'Jan-Mar'), (1,'Apr-Jun'), (2,'July-Sep'),(3,'Oct-Dec.');
    And then join to that table.
    Code:
    SELECT C.theYear, C.theQuarter, Q.qname, C.howMany
    FROM quarters AS Q,
         ( SELECT YEAR(download_date) AS theYear,
                FLOOR( ( MONTH(download_date) - 1 ) / 3 ) AS theQuarter,
                COUNT(*) AS howMany
           FROM software_downloads
           GROUP BY YEAR(download_date), FLOOR( ( MONTH(download_date) - 1 ) / 3 )
        ) AS C
    WHERE Q.qnum = C.theQuarter
    ORDER BY C.theYear, C.theQuarter
    But wouldn't it be simpler to use, say, an array in PHP code?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-20-2011)

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    Thank you soooo much for your kind help sir

  • #6
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    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

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Again, will you need this all done via SQL query or will you use PHP to help??

    The easiest way would be this:
    Code:
    SELECT YEAR(download_date) AS theYear,
           FLOOR( ( MONTH(download_date) - 1 ) / 3 ) AS theQuarter,
           download_by, 
           COUNT(*) AS howMany
    FROM software_downloads
    GROUP BY YEAR(download_date), FLOOR( ( MONTH(download_date) - 1 ) / 3 ), download_by
    ORDER BY theYear, theQuarter, download_by
    But that will get you separate records for admin and customer, separate rows in the report.

    If you *MUST* have them all in one row per quarter:
    Code:
    SELECT YEAR(download_date) AS theYear,
           FLOOR( ( MONTH(download_date) - 1 ) / 3 ) AS theQuarter,
           SUM( IF(download_by='admin',1,0) ) AS adminCount,
           SUM( IF(download_by='customer',1,0) ) AS customerCount
    FROM software_downloads
    GROUP BY YEAR(download_date), FLOOR( ( MONTH(download_date) - 1 ) / 3 )
    ORDER BY theYear, theQuarter
    And, as before, you can join that to another table to get the text forms of the quarter numbers.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-21-2011)

  • #8
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    Hi

    In the second query, you did not add "download_by" in the group by clause. Is that fine?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    GROUP BY only applies to fields that are *NOT* used in aggregate functions. SUM and COUNT are aggregate functions.

    Why didn't you just try it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (09-21-2011)

  • #10
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    I will once I reach work.

    Thanks for ur kind help sir.

  • #11
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    892
    Thanks
    301
    Thanked 2 Times in 2 Posts
    I tried this and it works like a charm.

    You are a genius


  •  

    Posting Permissions

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