List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 10 2006 10:52pm
Subject:Re: Using MySQL to log and report transactions
View as plain text  
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
Thread
Using MySQL to log and report transactionsJames Tu10 Nov
  • Re: Using MySQL to log and report transactionsDan Nelson10 Nov