How to Group Summing Totals by Month in MySQL?

Group Summing Totals by Month in MySQL

Question:

You're working with a table containing the columns "total" and "o_date" (order date). You need to calculate the sum of totals for each month, resulting in a grouped result where the key is the month (formatted as the month name) and the value is the total sum for that month.

Example Table:

| 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 |

Desired Result:

Month Name	Total
January	138
February	88.2
April	29.84

SQL Solution:

To achieve this result, use the following MySQL query:

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

Explanation:

• MONTHNAME(o_date) extracts the month name from the o_date column.
• SUM(total) calculates the sum of the total values for each group.
• GROUP BY YEAR(o_date), MONTH(o_date) groups the results by year and month, which effectively groups by the month alone.

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