In the last episode (Nov 10), James Tu said: > I'm going use MySQL to log transactions so that I can report on them > later. > Ex: > > CREATE TABLE statistics ( > id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT > , user_id BIGINT UNSIGNED > , entity_id INT UNSIGNED > , transaction_type INTEGER(2) UNSIGNED > , datetime_logged DATETIME > , datetime_logged_gmt DATETIME > , PRIMARY KEY (id) > ); > > > What are the queries that would get me the data organized by: > Months > Weeks > Days > Day of Week?
Use the date functions (day(), month(), etc) to extract the granularity you want, then group by those. SELECT DAY(datetime_logged), user_id, COUNT(*) FROM statistics GROUP BY DAY(datetime_logged), user_id; http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
