CODEWITHSUNDEEP
mysql
sam
sam

Reputation: 127

mysql - query to get most views in a day based on hour

i have the table "visitors" the fields are

    id = integer
    ip = char
    route = char
    created_at = timestamp

i want to count group by based on hour, i used the query

    SELECT * , HOUR(created_at) AS created_hour , COUNT(created_hour)
 FROM `visitors` WHERE created_at > DATE(NOW()) GROUP BY created_hour

but i get this error, i can "count" based on "group by"

SQL query: Documentation

SELECT * , HOUR(created_at) AS created_hour , COUNT(created_hour) FROM `visitors` WHERE created_at > DATE(NOW()) GROUP BY created_hour LIMIT 0, 25

MySQL said: Documentation
#1054 - Unknown column 'created_hour' in 'field list'

Upvotes: 0

Views: 254

Answers (2)

Kevin HR
Kevin HR

Reputation: 249

Change COUNT(created_hour) to COUNT(*).

Instead of WHERE created_at > DATE(NOW()), use WHERE created_at < DATE(NOW()). The current WHERE condition in your query will force nothing to be returned.

Upvotes: 3

waka
waka

Reputation: 3417

You use an alias from the SELECT clause in the same SELECT. This isn't possible, instead you'd have to use this:

SELECT HOUR(created_at) AS created_hour
     , COUNT(HOUR(created_at))
FROM `visitors`
WHERE created_at < DATE(NOW())
GROUP BY created_hour

Note that in MySQL it is indeed possible to use the alias in the GROUP BY. But it's just not possible to use it in the SELECT.

Also note that created_at > DATE(NOW()) really doesn't make much sense, unless you're a time traveller.

Also it would be better to use COUNT(*) instead of COUNT(HOUR(created_at)), it's more readable and it does return the same result.

Upvotes: 2

Related Questions