Solved

Multi-Month - Group by Month Query

Posted on 2010-09-23
4
448 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
  • 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 59

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search query matching words 20 38
SQL Server syntax 11 40
SQL Rewrite without the NULLIF 4 25
How to add a suffix to a value in a column based on the value in another column 4 30
Creating and Managing Databases with phpMyAdmin in cPanel.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

685 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