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)

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!