Author Topic: Using the SQL DT function to find aggregate totals by month  (Read 100 times)

0 Members and 1 Guest are viewing this topic.

Offline rpmolecule

  • Full Member
  • *
  • Posts: 228
  • Karma: +1/-0
  • Seize the Day!
    • View Profile
Using the SQL DT function to find aggregate totals by month
« on: December 09, 2008, 03:00:26 AM »
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?  

 
> EXPERT RESPONSE

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.

Techronnati | where technology never sleeps

Using the SQL DT function to find aggregate totals by month
« on: December 09, 2008, 03:00:26 AM »

Mountain View

 

Related Topics

  Subject / Started by Replies Last post
0 Replies
118 Views
Last post November 11, 2008, 04:52:14 AM
by Corps
0 Replies
99 Views
Last post April 02, 2009, 06:27:43 AM
by CyborgRepublic
3 Replies
138 Views
Last post September 10, 2014, 08:02:09 AM
by CyborgRepublic
0 Replies
64 Views
Last post December 14, 2016, 10:26:07 AM
by MrSpecialist
0 Replies
68 Views
Last post December 14, 2016, 10:40:34 AM
by MrSpecialist

Posting Disclaimer: Any individual may post a message in this forum and may do so anonymously. Therefore, the sole author is exclusively and entirely responsible for all opinions in that message. They do not represent the official opinions of Techronnati, its administrators or moderators or the Techronnati Management. Techronnati is merely acting as an impartial conduit for constitutionally protected free speech and is not responsible and will not be held liable for the content of such messages. All images and service logos are trademarks of their respective owners.