Solved

Multi-Month - Group by Month Query

Posted on 2010-09-23
4
450 Views
Last Modified: 2013-12-12
I need a query that produces a Multi-Month By Month Grouping...

For Example:

Table has entries with unique prices, and timestamps....  I need a Total Per Month, and Grouped by Month result.

Array might look like: (pseudo)

Orders{
  [JOHN]
    [TOTAL] [30000] [MONTH_TIMESTAMP or MONTH NAME]

}

Please ask more info as I likely need to provide you more information.



ORDER (table name)
  ID, SALES_PERSON_ID, TS_ENTERED, GROSS_PROFIT (fields)

USERS (table name)
 USERID

I need a Query where I retrieve total of all orders within a given span of months, and the same PER A SPECIFIC SALES PERSON (see above shortened table structure)


0
Comment
Question by:SMabs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:SMabs
ID: 33746240
Also, would it be EASIER to base dates on SQL Timestamp versus the UNIX Timestamp which I currently use for all timestamps in my databases...

Pros & Cons of the two different forms of time are appreciated.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33748280
On your question regarding using DATETIME verus TIMESTAMP, I would see this nice manual reference:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

The biggest thing to note is the range of supported date/time combinations.  Unix Timestamp is the number of seconds since the last EPOCH which is '1970-01-01', so you will see its range starts with '1970-01-01 00:00:01' and is limited to storage size of an integer (4 bytes) or 2^31 ; therefore, you will see that you can only store 2147483646 seconds or in other words up to '2038-01-19 03:14:07'.

Aside from this, you will have to use FROM_UNIXTIME if you want to display your date in a human readable format, which may be inconvenient.

Anyway for the select, you can do something similar to this once you have worked out details on the column -- I will show below with the date column as a datetime.

select u.USERNAME
   --, year(o.TS_ENTERED) as Yr
   --, month(o.TS_ENTERED) as Mo
     , monthname(o.TS_ENTERED) as Mo
     , sum(o.GROSS_PROFIT) as Totals
from ORDERS o
join USERS u on u.USERID = o.SALES_PERSON_ID
group by u.USERNAME
     --, year(o.TS_ENTERED)
     --, month(o.TS_ENTERED)
       , monthname(o.TS_ENTERED)
;

The commented out portions are other examples.  The Year may be required addition to ensure you separate 2009 data from 2010 for example, but if you are using a where clause to filter the data this may not be of consequence to you.  Additionally, you will find it hard to sort by month using the name, so may be interested in the month number instead.

Date function reference:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Hope that all helps!

Regards,
Kevin
0
 

Author Comment

by:SMabs
ID: 33771988
I will get back to this shortly....
0
 

Author Closing Comment

by:SMabs
ID: 33849980
thanks
0

Featured Post

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question