...

View Full Version : MySQL: Grouping date by quarterly and half yearly



phantom007
09-19-2011, 05:58 PM
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


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.

Old Pedant
09-19-2011, 06:29 PM
Arithmetic.



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



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.

phantom007
09-20-2011, 03:24 AM
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?

Old Pedant
09-20-2011, 04:58 AM
??? 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???



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.


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?

phantom007
09-20-2011, 06:51 AM
Thank you soooo much for your kind help sir :)

phantom007
09-20-2011, 06:59 AM
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



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

Old Pedant
09-20-2011, 08:33 PM
Again, will you need this all done via SQL query or will you use PHP to help??

The easiest way would be this:

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:


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.

phantom007
09-21-2011, 03:19 AM
Hi

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

Old Pedant
09-21-2011, 04:40 AM
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?

phantom007
09-21-2011, 04:43 AM
I will once I reach work.

Thanks for ur kind help sir.

phantom007
09-23-2011, 07:52 AM
I tried this and it works like a charm.

You are a genius :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum