Q

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

Let's propose a hypothetical example. I have several salespeople who sell widgets. In my DB the widget sales are listed by quantity sold per date by each salesperson. So, for example, Fred sells 2 widgets on 10/23/08 and John sells 5. Now on 10/24/08 Fred sells 14 and John sells 2. So on and so forth for a whole year. Each quantity is listed by date. But I want to group the dates together into months and then be able to see the widget sales by salesperson by month. How can I group the dates into months?
This is a straightforward application of GROUP BY with a date function.

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.

This was first published in November 2008

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close