Solved

Multi-Month - Group by Month Query

Posted on 2010-09-23
4
449 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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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 count occurrences of each item in an array.

751 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