Home >Database >Mysql Tutorial >How to Group Summing Totals by Month in MySQL?

How to Group Summing Totals by Month in MySQL?

Patricia Arquette
Patricia ArquetteOriginal
2024-11-21 05:41:10467browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn