Create a MySQL query to count records and GROUP BY every 7 days for a year?
Hello,
I want to produce a report that counts the number of records that are entered into our database on a weekly basis. Is there a way, just using MySQL, that I can loop through a year's worth of dates, doing a total for each of the 52 weeks? Or do I have to capture all of the data and then parse in a "for" loop in PHP? If the latter, can you help with the PHP code? If the former, can you supply the SQL syntax?
Here is what I have so far:
I want to produce a report that counts the number of records that are entered into our database on a weekly basis. Is there a way, just using MySQL, that I can loop through a year's worth of dates, doing a total for each of the 52 weeks? Or do I have to capture all of the data and then parse in a "for" loop in PHP? If the latter, can you help with the PHP code? If the former, can you supply the SQL syntax?
Here is what I have so far:
SELECT DateEntered,Count(ID) AS Total FROM Applications WHERE DateEntered Between '2007-01-01' AND '2007-01-06' Group By DateEntered
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
oh wait a minute...I just tried your query again and it works! I never knew about the %v parameter in DATE_FORMAT. Thanks......
did you look at what the query does?
It aggregates by year and week. A week is 7 days long.
if you have specific date ranges and know them, then you could do this:
CREATE TABLE IF NOT EXISTS `trick_table` (
`trick_id` bigint(20) NOT NULL,
`start_date` datetime NOT NULL,
`end_date` datetime NOT NULL,
PRIMARY KEY (`trick_id`)
) ENGINE=MyISAM
INSERT INTO `trick_table` (start_date,end_date)
VALUES ('2008-01-01','2008-01-06'
SELECT (SELECT COUNT(ID) FROM applications WHERE DateEntered Between tt.start_date AND tt.end_date ) FROM trick_table tt
It aggregates by year and week. A week is 7 days long.
if you have specific date ranges and know them, then you could do this:
CREATE TABLE IF NOT EXISTS `trick_table` (
`trick_id` bigint(20) NOT NULL,
`start_date` datetime NOT NULL,
`end_date` datetime NOT NULL,
PRIMARY KEY (`trick_id`)
) ENGINE=MyISAM
INSERT INTO `trick_table` (start_date,end_date)
VALUES ('2008-01-01','2008-01-06'
SELECT (SELECT COUNT(ID) FROM applications WHERE DateEntered Between tt.start_date AND tt.end_date ) FROM trick_table tt
ASKER
Yes, at first I didn't look carefully enough...but then realized that it aggregated by a week for a year. Thanks for your follow up.
Audrey
Audrey
no problem
ASKER
DateRange Total
2008-01-01 - 2008-01-06: 145
2008-01-07 - 2008-01-13: 98
2008-01-14-2008-01-20: 123
And so on.....
Is there a way to Automatically Count and Group By every 7 days for a set number of weeks?