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
dnelson@stripped
