Solved

Multi-Month - Group by Month Query

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
000webhost.com default error log 1 28
TSQL query to generate xml 4 35
How do uses indexes to maximize MySQL Searches 14 31
awk and Pythagoras? 5 7
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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.

777 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