{"id":8462,"date":"2020-12-16T05:12:00","date_gmt":"2020-12-16T05:12:00","guid":{"rendered":"https:\/\/ubiq.co\/tech-blog\/?p=8462"},"modified":"2025-08-29T05:01:59","modified_gmt":"2025-08-29T05:01:59","slug":"how-to-group-by-month-in-mysql","status":"publish","type":"post","link":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/","title":{"rendered":"How to Group By Month in MySQL"},"content":{"rendered":"\n<p>MySQL database is commonly used to store date time information such as sign ups, sales, payments, and other transactional data. It offers tons of functions and features to aggregate data by different time intervals such as years, months, weeks, days, etc. Often database programmers and data analysts need to aggregate data by month in MySQL. This is commonly required for online stores, apps, ecommerce businesses. It is also required for MIS reporting teams. In this article, we will look at different ways to group by month in MySQL.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#How_to_Group_By_Month_in_MySQL\" >How to Group By Month in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#1_Using_month_monthname_and_year\" >1. Using month, monthname and year<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Using_Month\" >Using Month()<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Using_MonthName\" >Using MonthName()<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Using_Year\" >Using Year()<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#2_Using_date_format\" >2. Using date_format<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Group_By_Month_Name\" >Group By Month Name<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Group_By_Month_and_Year\" >Group By Month and Year<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_to_Group_By_Month_in_MySQL\"><\/span>How to Group By Month in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are several simple ways to aggregate MySQL data by month. MySQL provides built-in functions such as year(), month() and day() to group data by year, month and day respectively. It also provides a powerful function date_format() that allows you to group data any way you want by simply changing the format string of its output. We will look at how to group by month in MySQL using each of these methods.<\/p>\n\n\n\n<p>Let us say you have a table <em>sales(id, order_date, amount)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; create table sales(id int, order_date date, amount int);<br><br>mysql&gt; insert into sales(id,order_date,amount)<br>     values(1,'2020-10-01',150),<br>     (2,'2021-10-10',100),<br>     (3,'2020-11-05',250),<br>     (4,'2021-11-15',150),<br>     (5,'2020-12-01',350),<br>     (6,'2021-12-21',250);<br><br>mysql&gt; select * from sales;<br>+------+------------+--------+<br>| id   | order_date | amount |<br>+------+------------+--------+<br>|    1 | 2020-10-01 |    150 |<br>|    2 | 2021-10-10 |    100 |<br>|    3 | 2020-11-05 |    250 |<br>|    4 | 2021-11-15 |    150 |<br>|    5 | 2020-12-01 |    350 |<br>|    6 | 2021-12-21 |    250 |<br>+------+------------+--------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Using_month_monthname_and_year\"><\/span>1. Using month, monthname and year<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>MySQL provides many built in functions such as month(), monthname() and year() to aggregate data at different levels of granularity. We will use them to obtain monthly data.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_Month\"><\/span>Using Month()<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Month() function returns the month number of a date string. You can pass this string as a literal value of column name. Here is a SQL query to extract month number from order_date column and aggregates the value of amount column to obtain monthly sales numbers.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT MONTH(order_date) AS month, sum(amount) AS total_sales <br>       FROM sales <br>       GROUP BY MONTH(order_date);<br>+-------+-------------+<br>| month | total_sales |<br>+-------+-------------+<br>|    10 |         250 |<br>|    11 |         400 |<br>|    12 |         600 |<br>+-------+-------------+<\/pre>\n\n\n\n<p><strong>Please note<\/strong>, you need to mention the month() function in select clause as well as group by clause. If you do not mention it in group by clause, MySQL will not do the aggregation. Here is an example where we have not mentioned month() function in group by.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT MONTH(order_date) AS month, sum(amount) AS total_sales <br>       FROM sales <br>       <strong>GROUP BY order_date;<\/strong><br>+-------+-------------+<br>| month | total_sales |<br>+-------+-------------+<br>|    10 |         150 |<br>|    10 |         100 |<br>|    11 |         250 |<br>|    11 |         150 |<br>|    12 |         350 |<br>|    12 |         250 |<br>+-------+-------------+<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_MonthName\"><\/span>Using MonthName()<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>monthname() function returns the name of the month, instead of month number as in case of month() function. It&#8217;s syntax is similar to that of month() function. Here is the SQL query to aggregate data by month name.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT MONTHNAME(order_date) AS month, sum(amount) AS total_sales <br>       FROM sales <br>       GROUP BY MONTHNAME(order_date);<br>+----------+-------------+<br>| month    | total_sales |<br>+----------+-------------+<br>| December |         600 |<br>| November |         400 |<br>| October  |         250 |<br>+----------+-------------+<\/pre>\n\n\n\n<p><strong>Please note<\/strong>, here also you need to mention monthname() function in both select as well as group by clause for proper aggregation. Secondly, the result of monthname() function is sorted alphabetically whereas the result of month() function is sorted numerically or chronologically.<\/p>\n\n\n\n<p>If you want to chronologically sort output of monthname() function, then use monthname() only in select clause but use month() function in group by as shown below.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT <strong>MONTHNAME(order_date)<\/strong> AS month, sum(amount) AS total_sales <br>       FROM sales <br>       GROUP BY <strong>MONTH(order_date)<\/strong>;<br>+----------+-------------+<br>| month    | total_sales |<br>+----------+-------------+<br>| October  |         250 |<br>| November |         400 |<br>| December |         600 |<br>+----------+-------------+<\/pre>\n\n\n\n<p>The above output is not sorted alphabetically but chronologically.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Using_Year\"><\/span>Using Year()<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>MySQL also provides year() function that extracts year number from a date. If your data runs into multiple years, as in our case, then grouping data based on month alone will end up aggregating data of different years into a single month. In such cases, you will need to group by month as well as year. Here is an SQL query to group data by year and month.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT YEAR(order_date),MONTH(order_date) AS month, <br>       sum(amount) AS total_sales <br>       FROM sales <br>       GROUP BY year(order_date), month(order_date);<br>+------------------+-------+-------------+<br>| YEAR(order_date) | month | total_sales |<br>+------------------+-------+-------------+<br>|             2020 |    10 |         150 |<br>|             2020 |    11 |         250 |<br>|             2020 |    12 |         350 |<br>|             2021 |    10 |         100 |<br>|             2021 |    11 |         150 |<br>|             2021 |    12 |         250 |<br>+------------------+-------+-------------+<\/pre>\n\n\n\n<p>Please note, you will need to mention both year() and month() functions in SELECT as well as GROUP BY clauses of your SQL queries. Otherwise, the aggregation will not work properly.<\/p>\n\n\n\n<p>In above case, the groups are sorted by year first and within each year, they are sorted by month. You can change the order of your output using ORDER BY clause as shown below. Here we have sorted the year in descending order, using desc keyword and within each year, we have sorted the month in ascending order.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT YEAR(order_date),MONTH(order_date) AS month, <br>        sum(amount) AS total_sales <br>        FROM sales <br>        GROUP BY year(order_date), month(order_date) <br>        <strong>ORDER BY year(order_date) desc, month(order_date)<\/strong>;<br>+------------------+-------+-------------+<br>| YEAR(order_date) | month | total_sales |<br>+------------------+-------+-------------+<br>|             2021 |    10 |         100 |<br>|             2021 |    11 |         150 |<br>|             2021 |    12 |         250 |<br>|             2020 |    10 |         150 |<br>|             2020 |    11 |         250 |<br>|             2020 |    12 |         350 |<br>+------------------+-------+-------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Using_date_format\"><\/span>2. Using date_format<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Date_format() is a powerful function that allows you to format your date into any kind of string. We will use <em>date_format<\/em> function to format date into month values and group by month.<\/p>\n\n\n\n<p>Here is the syntax of date_format function.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">date_format(value, format string)<\/pre>\n\n\n\n<p>In the above function, you need to specify value as a literal, another function or column name, and a format string that specifies the format to which this value needs to be converted to. <a href=\"https:\/\/www.w3schools.com\/sql\/func_mysql_date_format.asp\" target=\"_blank\" rel=\"noreferrer noopener\">Here<\/a> is a list of format strings supported by date_format.<\/p>\n\n\n\n<p>Here is an example to convert a date string into month.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; SELECT DATE_FORMAT(\"2020-06-15\", \"%M\");<br>+---------------------------------+<br>| DATE_FORMAT(\"2020-06-15\", \"%M\") |<br>+---------------------------------+<br>| June                            |<br>+---------------------------------+<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Group_By_Month_Name\"><\/span>Group By Month Name<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Here is the SQL query to group by month name using %M format specifier.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; select date_format(order_date, '%M'),sum(amount)<br>       from sales<br>       group by date_format(order_date, '%M');<br>+-------------------------------+-------------+<br>| date_format(order_date, '%M') | sum(amount) |<br>+-------------------------------+-------------+<br>| December                      |         600 |<br>| November                      |         400 |<br>| October                       |         250 |<br>+-------------------------------+-------------+<\/pre>\n\n\n\n<p>In the above SQL query we use <em>date_format(order_date, &#8220;%M&#8221;)<\/em>  to convert a date column into month name, and use <em>sum<\/em> column to add up the sales amounts.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Group_By_Month_and_Year\"><\/span>Group By Month and Year<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Here is the SQL query to group by month and year.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; select date_format(order_date, '%M %Y'),sum(amount)<br>       from sales<br>       group by year(order_date),month(order_date);<br>+----------------------------------+-------------+<br>| date_format(order_date, '%M %Y') | sum(amount) |<br>+----------------------------------+-------------+<br>| October 2020                     |         150 |<br>| November 2020                    |         250 |<br>| December 2020                    |         350 |<br>| October 2021                     |         100 |<br>| November 2021                    |         150 |<br>| December 2021                    |         250 |<br>+----------------------------------+-------------+<br><\/pre>\n\n\n\n<p>In the above query, we use date_format to convert date column&#8217;s values into month and year names. We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers.<\/p>\n\n\n\n<p>In the above query, if we use <em>date_format<\/em> function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically. See below.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; select date_format(order_date, '%M'),sum(amount)<br>       from sales<br>       group by date_format(order_date, '%M');<br>+-------------------------------+-------------+<br>| date_format(order_date, '%M') | sum(amount) |<br>+-------------------------------+-------------+<br>| December                      |         600 |<br>| November                      |         400 |<br>| October                       |         250 |<br>+-------------------------------+-------------+<\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In this article, we have learnt several different ways to group data by month in MySQL. You can use any of them as per your requirement. But there are some important things to keep in mind. First of all, you need to mention the aggregation function month(), year(), monthname() or date_format() in both SELECT as well as GROUP BY parts of your SQL query. If you do not mention them in GROUP BY clause, then MySQL will not do the aggregation properly.<\/p>\n\n\n\n<p>Second, remember that month() and year() functions will sort the output chronologically while month_name() and date_format() will sort the output alphabetically. You can always change the sort order by adding ORDER BY clause as we have shown above.<\/p>\n\n\n\n<p>We have covered many subtle nuances about this problem in this article. Please go through them carefully and use the appropriate solution to avoid any pitfalls.<\/p>\n\n\n\n<p>Need a reporting tool for MySQL? <a href=\"http:\/\/ubiq.co\/\">Ubiq<\/a>&nbsp;makes it easy to visualize data in minutes, and monitor in real-time dashboards.&nbsp;<a href=\"http:\/\/ubiq.co\/accounts\/register\">Try it<\/a>&nbsp;Today!<\/p>\n\n\n\n<p>Also read:<br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-remove-not-null-constraint-in-mysql\/\">How to Remove Not Null Constraint in MySQL<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-remove-default-value-of-column-in-mysql\/\">How to Remove Default Column Value in MySQL<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-round-timestamp-in-mysql\/\">How to Round Timestamp in MySQL<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL.<\/p>\n","protected":false},"author":1,"featured_media":8464,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[324],"tags":[463],"class_list":["post-8462","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-group-by-month"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to Group By Month in MySQL - Ubiq BI<\/title>\n<meta name=\"description\" content=\"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Group By Month in MySQL - Ubiq BI\" \/>\n<meta property=\"og:description\" content=\"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Ubiq BI\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ubiqbi\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-16T05:12:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-29T05:01:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"730\" \/>\n\t<meta property=\"og:image:height\" content=\"410\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Sreeram Sreenivasan\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:site\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sreeram Sreenivasan\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\"},\"author\":{\"name\":\"Sreeram Sreenivasan\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\"},\"headline\":\"How to Group By Month in MySQL\",\"datePublished\":\"2020-12-16T05:12:00+00:00\",\"dateModified\":\"2025-08-29T05:01:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\"},\"wordCount\":1023,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1\",\"keywords\":[\"group by month\"],\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\",\"url\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\",\"name\":\"How to Group By Month in MySQL - Ubiq BI\",\"isPartOf\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1\",\"datePublished\":\"2020-12-16T05:12:00+00:00\",\"dateModified\":\"2025-08-29T05:01:59+00:00\",\"author\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\"},\"description\":\"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().\",\"breadcrumb\":{\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage\",\"url\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1\",\"width\":730,\"height\":410,\"caption\":\"group by month in mysql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ubiq.co\/tech-blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Group By Month in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#website\",\"url\":\"https:\/\/ubiq.co\/tech-blog\/\",\"name\":\"Ubiq BI\",\"description\":\"Build dashboards &amp; reports in minutes\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/ubiq.co\/tech-blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc\",\"name\":\"Sreeram Sreenivasan\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"caption\":\"Sreeram Sreenivasan\"},\"description\":\"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.\",\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/sreeram-sreenivasan\/\"],\"url\":\"https:\/\/ubiq.co\/tech-blog\/author\/wordpress\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Group By Month in MySQL - Ubiq BI","description":"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"How to Group By Month in MySQL - Ubiq BI","og_description":"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().","og_url":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/","og_site_name":"Ubiq BI","article_publisher":"https:\/\/www.facebook.com\/ubiqbi","article_published_time":"2020-12-16T05:12:00+00:00","article_modified_time":"2025-08-29T05:01:59+00:00","og_image":[{"width":730,"height":410,"url":"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp","type":"image\/webp"}],"author":"Sreeram Sreenivasan","twitter_card":"summary_large_image","twitter_creator":"@UbiqBI","twitter_site":"@UbiqBI","twitter_misc":{"Written by":"Sreeram Sreenivasan","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#article","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/"},"author":{"name":"Sreeram Sreenivasan","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"headline":"How to Group By Month in MySQL","datePublished":"2020-12-16T05:12:00+00:00","dateModified":"2025-08-29T05:01:59+00:00","mainEntityOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/"},"wordCount":1023,"commentCount":0,"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1","keywords":["group by month"],"articleSection":["MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/","url":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/","name":"How to Group By Month in MySQL - Ubiq BI","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage"},"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1","datePublished":"2020-12-16T05:12:00+00:00","dateModified":"2025-08-29T05:01:59+00:00","author":{"@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"description":"Sometimes you may need to aggregate by month in MySQL. Here is how to group by month in MySQL using month(), year() and date_format().","breadcrumb":{"@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#primaryimage","url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1","contentUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1","width":730,"height":410,"caption":"group by month in mysql"},{"@type":"BreadcrumbList","@id":"https:\/\/ubiq.co\/tech-blog\/how-to-group-by-month-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ubiq.co\/tech-blog\/"},{"@type":"ListItem","position":2,"name":"How to Group By Month in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/ubiq.co\/tech-blog\/#website","url":"https:\/\/ubiq.co\/tech-blog\/","name":"Ubiq BI","description":"Build dashboards &amp; reports in minutes","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ubiq.co\/tech-blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc","name":"Sreeram Sreenivasan","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","caption":"Sreeram Sreenivasan"},"description":"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.","sameAs":["https:\/\/www.linkedin.com\/in\/sreeram-sreenivasan\/"],"url":"https:\/\/ubiq.co\/tech-blog\/author\/wordpress\/"}]}},"jetpack_featured_media_url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2020\/12\/group-by-month-mysql.webp?fit=730%2C410&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/pbGGTT-2cu","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/comments?post=8462"}],"version-history":[{"count":2,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8462\/revisions"}],"predecessor-version":[{"id":9361,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8462\/revisions\/9361"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media\/8464"}],"wp:attachment":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media?parent=8462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/categories?post=8462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/tags?post=8462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}