Home > Database > Mysql Tutorial > How to Calculate Monthly Sums from Daily Totals Using SQL's GROUP BY and MONTHNAME?

How to Calculate Monthly Sums from Daily Totals Using SQL's GROUP BY and MONTHNAME?

Susan Sarandon
Release: 2024-11-16 08:19:03
Original
716 people have browsed it

How to Calculate Monthly Sums from Daily Totals Using SQL's GROUP BY and MONTHNAME?

Using GROUP BY and MONTHNAME to Calculate Monthly Sums

You have a table containing a column of daily totals named 'total' and a column of dates named 'o_date'. To calculate the monthly sums and group the results by month, you can use the following SQL query:

SELECT MONTHNAME(o_date), SUM(total) 
FROM theTable
GROUP BY YEAR(o_date), MONTH(o_date)
Copy after login

Let's break down the query:

  • MONTHNAME(o_date): This function extracts the month name from the 'o_date' column.
  • SUM(total): This function calculates the sum of the 'total' column.
  • GROUP BY YEAR(o_date), MONTH(o_date): This clause groups the results by year and month, so you get the monthly sums for each unique month.

The query will produce a result set with two columns:

  • MONTHNAME(o_date): The month name for each row.
  • SUM(total): The sum of 'total' for the corresponding month.

Based on the sample data you provided, the query will return the following result:

Month Total
January 138
February 88.2
April 29.84

The above is the detailed content of How to Calculate Monthly Sums from Daily Totals Using SQL's GROUP BY and MONTHNAME?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template