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:

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

Code (markup):

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.

 

To do it by quarter this is the SQL:

SELECT COUNT(id) AS DownloadTotal, YEAR(download_date) AS ReportYear, (ROUND(MONTH(download_date)/4)+1) AS ReportQuarter 
FROM golf_matches 
GROUP BY YEAR(download_date),(ROUND(MONTH(download_date)/4)+1) 
ORDER BY YEAR(download_date),(ROUND(MONTH(download_date)/4)+1);

PHP:

To do it for a different time frame requires you to change this piece of code wherever it is found:

(ROUND(MONTH(download_date)/4)+1)

For semi-annually you could simply change that code to an IIF statement based on the month being >7

 

Sir, I actually want the date to group by Jan-Mar, Apr-Jun, July-Sep, Oct-Dec.

Please help me with the query

 

One more query.

Please consider the following DDL

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

Code (markup):

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

 

It does group like you want it too, it just represents those time frames as numbers (ReportQuarter=1 groups Jan - Mar, ReportQuarter=2 groups Apr-Jun). Making it display as you want would best be handled programmatically (using your scripting language, i.e. php, asp, etc).

As for admin/customer downloads, add the download_by field to the SELECT and GROUP BY clauses of the SQL. Like this:

SELECT download_by, COUNT(id) AS DownloadTotal, YEAR(download_date) AS ReportYear, (ROUND(MONTH(download_date)/4)+1) AS ReportQuarter FROM golf_matches GROUP BY download_by, YEAR(download_date),(ROUND(MONTH(download_date)/4)+1) ORDER BY YEAR(download_date),(ROUND(MONTH(download_date)/4)+1);

PHP:

 

Thank you very much for your kind help.