At 17:22 -0400 4/16/04, Mike Morton wrote:
I have a table:
CREATE TABLE `moviehits` (
`title` varchar(5) NOT NULL default '',
`movie` varchar(4) NOT NULL default '',
`hit_date` datetime NOT NULL default '0000-00-00 00:00:00',
`ip` varchar(15) NOT NULL default '',
`listing_id` int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
That I would like to grab stats by title, something along the following:
select count(movie),hit_date,movie from moviehits where hit_date between
'2004-04-01' and '2004-04-31' and title='33329' group by hit_date
Of course, when I do the group by for the hit_date, it does not group hits
by day because of the time element.
Is there a way to use the existing database structure to get hits by date
using a group by clause of some sort?
This query will group by the date part of the hit_date column, showing
the number of records per day in the table:
SELECT FROM_DAYS(TO_DAYS(hit_date)) AS day, COUNT(*)
FROM moviehits GROUP BY day;
The trick is that FROM_DAYS(TO_DAYS(x)), where x is a DATETIME or TIMESTAMP,
strips off the time to produce just the date part.
You can adapt it to your own situation. However, if you're grouping
by the day, but trying to count each movie per day, you may want to group
by day and movie.
select count(movie),hit_date,movie from moviehits where hit_date between
'2004-04-01' and '2004-04-31' and title='33329' group by
date_format(hit_date,"%Y-$m-%d")
Or something along those lines?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
