How to group data by month in MySQL?

Asked By 240 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by Stella
Answered By 0 points N/A #97133

How to group data by month in MySQL?

qa-featured

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.

Answered By 240 points N/A #97134

How to group data by month in MySQL?

qa-featured

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.

Answered By 240 points N/A #97135

How to group data by month in MySQL?

qa-featured

I forgot to add. I need to get the total of the "hoursWorked" broken down by month.

Answered By 0 points N/A #97137

How to group data by month in MySQL?

qa-featured

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.

Answered By 240 points N/A #97138

How to group data by month in MySQL?

qa-featured

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?

Best Answer
Best Answer
Answered By 0 points N/A #97140

How to group data by month in MySQL?

qa-featured

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). 🙂

Answered By 240 points N/A #97142

How to group data by month in MySQL?

qa-featured

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 0 points N/A #97144

How to group data by month in MySQL?

qa-featured

You are welcome! Have a nice day!

Related Questions