I have data in my table like following:
stuff stuff stuff stuff date_viewed
whtever whtever whtever whtever 2018-05-15 20:58:31
whtever whtever whtever whtever 2018-05-13 15:32:22
whtever whtever whtever whtever 2018-05-15 23:58:44
whtever whtever whtever whtever 2018-05-05 13:21:32
whtever whtever whtever whtever 2018-05-12 13:21:32
whtever whtever whtever whtever 2018-05-14 12:21:32
whtever whtever whtever whtever #more dates from today and other days...
I need to order items from today, but by most popular ones, for example:
Item( today views 20 )
Item2( today views 18 )
Item3( today views 14 )
so on...
How can I accomplish this?
For simplification I assume that the table is named table and only has the two columns stuff (some kind of ID or string or whatever you have) and the column date_viewed which contains the date and time of the view.
In this case, you query could look like this:
SELECT stuff, DATE(date_viewed), COUNT(*) AS views FROM `table`
GROUP BY stuff, DATE(date_viewed)
ORDER BY views DESC
What is the query doing? * It selects stuff, together with the date part of date_viewed, and a count of rows calculated by the COUNT() function and stored under the alias (AS) name views from the table. * The result is then grouped by stuff and the date, i.e. all entries with the same item ("stuff") and date are put into one result row and the views hold the number of original rows. * Finally, the result is sorted by (ORDER BY) number of views (views, which was an alias for COUNT(*)) in descending order (DESC), so that rows with most views go first.