MySQL - calculate sales in a time period by hour

[wrightee]

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.

[Zipper]

You mean something like this,

SELECT * FROM tbl_sales WHERE TIME(transaction_timedate) BETWEEN '09:00' AND '17:00'

[wrightee]

No. I mean the result would be a list of dates with the total sales made for each day between 9 & 5pm for example.

[coopster]

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.