How to group data by month in MySQL?

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?

• Status: Open
• Question Views: 2050
• Answer Count: 8
• Vote Up 24 Vote Down

• Answer Accepted: Yes
• Question Category: MySQL Server

Best Answer by Stella

Go To Solution

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Answered By Josaphine 240 points N/A #97134

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.

About Josaphine

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Answered By Josaphine 240 points N/A #97135

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.

About Josaphine

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

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.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Answered By Josaphine 240 points N/A #97138

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?

About Josaphine

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Best Answer

Best Answer

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

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Answered By Josaphine 240 points N/A #97142

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!

About Josaphine

Questions
1

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server

Answered By Stella 0 points N/A #97144

How to group data by month in MySQL?

You are welcome! Have a nice day!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/15/2011
• Question Category: MySQL Server