Welcome to WebmasterWorld Guest from 18.208.159.25

Forum Moderators: open

Message Too Old, No Replies

MySQL - calculate sales in a time period by hour

     
3:59 pm on May 27, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Mar 17, 2005
posts: 109
votes: 0


Hi - I can't figure out this one:

tbl_sales
- transaction_timedate
- amount

If I want to know sales on a daily basis in a particular hourly time frame (e.g. only counting sales that happened between 9:00 and 17:00) - how would I write it? My efforts so far have yielded rubbish I'm ashamed to say.

Thanks in advance for your time.

7:08 pm on May 27, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:June 3, 2004
posts:193
votes: 0


You mean something like this,

SELECT * FROM tbl_sales WHERE TIME(transaction_timedate) BETWEEN '09:00' AND '17:00'
9:42 pm on May 27, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Mar 17, 2005
posts: 109
votes: 0


No. I mean the result would be a list of dates with the total sales made for each day between 9 & 5pm for example.
11:06 pm on May 27, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12555
votes: 3


That query is almost complete, wrightee. All you need to do is SELECT the DATE portion of the DATETIME column, perform your SUM() and GROUP BY on the DATE portion of the DATETIME column.