Asked By
Josaphine
240 points
N/A
Posted on - 05/15/2011
How to group data by month when using a date time column in MySQL? I want to produce a report, to group the results by month. The table has a column for which the data type is DateTime. How to use it, to get the results by month?
Answered By
Stella
0 points
N/A
#97133
How to group data by month in MySQL?
Can you post the table structure so that we can understand the query that you need to write? It will make the solution much easier.
How to group data by month in MySQL?
Following is the Create Table statement in question:
CREATE TABLE `timelog` (
`Id` int(11) NOT NULL auto_increment,
`fk_client` int(11) unsigned default NULL,
`logDate` date default NULL,
`createdDate` datetime default NULL,
`fk_user` int(11) unsigned default NULL,
`hoursWorked` float unsigned default NULL,
`fk_category` int(11) unsigned default NULL,
PRIMARY KEY (`Id`),
) ENGINE=InnoDB AUTO_INCREMENT=8972 DEFAULT CHARSET=latin1;
I need to group the results by month using the “createdDate” field.
How to group data by month in MySQL?
I forgot to add. I need to get the total of the "hoursWorked" broken down by month.
Answered By
Stella
0 points
N/A
#97137
How to group data by month in MySQL?
You can use the "month" function in MySQL, to get the month off the DateTime field. You can use it in the group by clause to get the desired result. Please note that this function is MySQL specific and will not work in any other database.
SELECT MONTH(hoursWorked), SUM(hoursWorked)
FROM timelog
GROUP BY MONTH(hoursWorked)
This will break up the total hoursworked by month.
How to group data by month in MySQL?
Thank you Stella for the response.
I tried your query. It shows the month number in the first column and then the total. I also checked against a manual count. It looks like it is summing up for all the data in the table. I have data across two years. How do I break it up, so that it actually shows the correct total for each month in the year?
Answered By
Stella
0 points
N/A
#97140
How to group data by month in MySQL?
You will need to further modify the query, to break it up by year and then by month. Interestingly MySQL defines a "year" function that returns the year value, given a datetime.
SELECT YEAR(createdDate), MONTH(createdDate), SUM(hoursWorked)
FROM timelog
GROUP BY YEAR(createdDate), MONTH(createdDate)
The above query will first group it by the year and then break it up by months.
PS: I made an error in the previous post, it should be MONTH(createdDate) and not MONTH(hoursWorked). 🙂
How to group data by month in MySQL?
THANK YOU! The new query works! Yes I did notice the error in the first query. The results are accurate now! Thank you Stella, for helping me with this issue. Now I can resume from where I got stuck!
Answered By
Stella
0 points
N/A
#97144
How to group data by month in MySQL?
You are welcome! Have a nice day!
