Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multi-Month - Group by Month Query

Posted on 2010-09-23
4
Medium Priority
?
451 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 2000 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

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

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
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

715 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