How to Group and Sum Values by Month in MySQL?

MySQL Query for Grouping and Summing Values by Date

Question:

Given a table with total and date columns, how can we group the total values by month and retrieve the aggregate sum for each month?

Example Data:

total	o_date
35	01-11-2009 19:32:44
41.5	01-12-2009 22:33:49
61.5	01-23-2009 22:08:24
66	02-01-2009 22:33:57
22.22	02-01-2009 22:37:34
29.84	04-20-2009 15:23:49

Expected Output:

month	total
Jan	138
Feb	88.2
Apr	29.84

Solution:

To achieve the desired result, we can use the following MySQL query:

SELECT MONTHNAME(o_date) AS month, SUM(total)
FROM theTable
GROUP BY YEAR(o_date), MONTH(o_date);

Explanation:

• The MONTHNAME() function is used to extract the month name from the o_date column.
• The SUM() function aggregates the total values for each month.
• The GROUP BY clause groups the results by the month name.

As a result, the query will return a table with two columns: month (the month name) and total (the sum of the total values for each month).

The above is the detailed content of How to Group and Sum Values by Month in MySQL?. For more information, please follow other related articles on the PHP Chinese website!