Using the SQL date function to find aggregate totals by month
Read SQL expert Rudy Limeback's solution for using an SQL date function, EXTRACT, to find aggregate totals by month
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
The only real problem here is which date function you can use. Date functions actually precede the adoption of SQL Standards for temporal datatypes, so many database systems have their own, proprietary functions. In Standard SQL, the function you need here is EXTRACT.
SELECT EXTRACT(MONTH FROM salesdate) AS salesmonth , salesperson , SUM(salesqty) AS total_sales FROM widget_sales GROUP BY EXTRACT(MONTH FROM salesdate) , salesperson ORDER BY EXTRACT(MONTH FROM salesdate) , total_sales DESC
This query will combine monthly sales for all years, if the table contains data for more than one year (although your question seems to suggest that it doesn't).
Notice that the ORDER BY clause sorts the results into months, and then within each month, by decreasing total sales.