0

I am trying to create a report to understand the time-of-day that orders are being placed, so I need to sum and group them by time. For example, I would like a sum of all orders placed between 1 and 1:59, then the next row listing the sum of all orders between 2:00 and 2:59, etc. The field is a datetime variable, but for the life me I haven't been able to find the right query to do this. Any suggestions sending me down the right path would be greatly appreciated.

Thanks

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
jimmyb
  • 1
  • 1

2 Answers2

1

If by luck it is mysql and by sum of orders you mean the number of orders and not the value amount:

select date_format(date_field, '%Y-%m-%d %H') as the_hour, count(*)
from my_table
group by the_hour
order by the_hour
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Definitely getting closer. You are correct, mysql and number of orders. I have about 1600 rows in my table, but it appears to be counting by day, and not across all dates. Here's a partial result from the query: `2010-06-17 12:00:00 1 2010-07-28 22:00:00 1 2010-07-29 00:00:00 1 2010-07-29 07:00:00 1 2010-07-29 11:00:00 1 2010-07-29 20:00:00 2 2010-07-29 21:00:00 1 2010-07-30 09:00:00 1 2010-07-30 10:00:00 2 2010-07-30 11:00:00 1 2010-07-30 12:00:00 1 2010-07-30 15:00:00 1 2010-07-30 18:00:00 1 2010-07-30 19:00:00 1 2010-07-30 20:00:00 1 2010-07-30 21:00:00 1 2010-07-31 02:00:00 1 2010-0` – jimmyb Mar 28 '11 at 22:20
  • Yes, the query was against all 1600 rows. I should have been more clear, the query produced a result of 1100 hundred rows (this forum only let me copy 20 or so rows), but it breaks down into date:hour, rather then counting orders for each hour across all days (for a total of 24 rows). For example, July 29, 2010 (see above) had 6 orders, 1 at midnight, 1 at 11am, 2 at 8pm, etc. I'm just not sure how to combine all the days and break out the result by hour. Here's my Query: SELECT STR_TO_DATE( DATETIME, '%Y-%m-%d %H' ) AS the_hour, COUNT( * ) FROM orders GROUP BY the_hour ORDER BY the_hour – jimmyb Mar 28 '11 at 22:56
  • So very, very close. ;) Once I looked a little closer and realized how you were trying to accomplish this, I realized that we only want the hour component rather than the entire date. Success! So the final result is: `SELECT DATE_FORMAT( DATETIME, '%H' ) AS the_hour, COUNT( * ) FROM orders GROUP BY the_hour ORDER BY the_hour` Thanks for your help! – jimmyb Mar 28 '11 at 23:17
0

This king of grouping (using a calculated field) will certainly not scale over time. If you really need to execute this specific GROUP BY/ORDER BY frequently, you should create an extra field (an UNSIGNED TINYINT field will suffice) storing the hour and place an INDEX on that column.

That is of course if your table is becoming quite big, if it is small (which cannot be stated in mere number of records because it is actually a matter of server configuration and capabilities as well) you won't probably notice much difference in performance.

georgepsarakis
  • 1,927
  • 3
  • 20
  • 24