MySQL query to count total number of orders made by hour returns always 1 for each hour

Question

I have following query which is supposed to count the total number of orders made by hour.

The problem is that total number is always 1 per hour. I have similar query for weekdays which works just fine. What is wrong or missing in the following query?

SELECT COUNT(`Orderno`) as 'totals', EXTRACT(HOUR FROM `order_datetime`) as 'hour'
FROM `orders`
GROUP BY `order_datetime`

Results are

totals hour
1      0
1      1
1      2
...

`GROUP BY order_datetime` will only group rows together if they have the exact same time, not the same hour. — Barmar, Oct 12 '16 at 17:09
You also have some quoting issues, see http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Oct 12 '16 at 17:10

score 1 · Accepted Answer · answered Oct 12 '16 at 17:17

1

SELECT COUNT(`Orderno`) as 'totals', EXTRACT(HOUR FROM `order_datetime`) as   'hour'
FROM `orders`
GROUP BY `order_datetime`,EXTRACT(HOUR FROM `order_datetime`)

Try it

answered Oct 12 '16 at 17:17

Pankaj Rawat

4,037
6
41
73

Great, thanks! Just a small modification to your suggestion made it work: GROUP BY EXTRACT(HOUR FROM `order_datetime`) – user1271930 Oct 12 '16 at 17:22

score 0 · Answer 2 · answered Oct 12 '16 at 17:10

0

group by hour(order_datetime)

should do it.

answered Oct 12 '16 at 17:10

raphael75

2,982
4
29
44

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/13963322) – zinking Oct 13 '16 at 01:35
@zinking Looks good to me... It would be nice if it had a bit more explanation, but it *does* attempt to answer the question. – Mike Oct 13 '16 at 15:04

MySQL query to count total number of orders made by hour returns always 1 for each hour

2 Answers2