MySQL: Grouping date by quarterly and half yearly

cancer10 · #1 (**permalink**) 09-19-11, 12:57

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.

it-iss.com · #2 (**permalink**) 09-19-11, 14:58

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.

r937 · #3 (**permalink**) 09-19-11, 15:19

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

cancer10 · #4 (**permalink**) 09-19-11, 22:22

Quote:

Originally Posted by it-iss.com

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

cancer10 · #5 (**permalink**) 09-20-11, 02:02

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

healdem · #6 (**permalink**) 09-20-11, 03:13

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.