From: Dan Nelson
Date: November 10 2006 10:52pm
Subject: Re: Using MySQL to log and report transactions
List-Archive: http://lists.mysql.com/mysql/203285
Message-Id: <20061110225225.GA79872@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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