Implementing MySQL Monthly Statistics tutorial

From:http://www.jbxue.com/db/758.html

Implementing MySQL Monthly Statistics tutorial

MySQL has a field is a DateTime type, to implement can be counted by month , how to write SQL statement?
Select Month (F1) from the TT Group by month (F1)
Or

Select Date_format (F1, '%m ') from the TT GROUP by Date_format (F1, '%m ')

For example, the database is 2008-01-15 12:10:00
The results of the Date_format parameter format are:
'%Y ' 2008
'%y-%m ' 2008-01
'%y-%c ' 2008-1
'%m ' 01
'%c ' 1

Date_format Format Description:
Format description
%a Abbreviated Week name
%b Abbreviated month name
%c month, numeric
%d Day of the month with English prefixes
%d months of the day, value (00-31)
%e Month Day, value (0-31)
%f Subtle
%H hours (00-23)
%h hours (01-12)
%I Hours (01-12)
%i min, numeric (00-59)
Day of the%j year (001-366)
%k hours (0-23)
%l hours (1-12)
%M Month Name
%m month, value (00-12)
%p am or PM
%r time, 12-hour (Hh:mm:ss am or PM)
%s seconds (00-59)
%s seconds (00-59)
%T time, 24-hour (HH:MM:SS)
%u week (00-53) Sunday is the first day of the week
%u week (00-53) Monday is the first day of the week
%V Week (01-53) Sunday is the first day of the week, with%x
%v Week (01-53) Monday is the first day of the week, with%x
%W Week Name
%w Day of the week (0= Sunday, 6 = Saturday)
%x year, of which Sunday was the first day of the week, 4-bit, with%v used
%x year, of which Monday was the first day of the week, 4-bit, with%v used
%Y year, 4 people
%y year, 2 people

Save data by quarter
Select year (PROCUREMENT_DT) *10+ ((Month (PROCUREMENT_DT)-1) DIV 3) +1, MONTH (PROCUREMENT_DT), Procurement_dt from xs001
Groupbyconcat (Date_format (savetime, '%Y '), Floor ((Date_format (savetime, '%m ') +2)/3))

An additional monthly statistical method

mysql-Monthly Statistic Data
Statistics of 2010 Monthly funds

Select Date_format (date, '%y-%m ') as Month,sum (money) as money from finance where date_format (date, '%Y ') =2010 GROUP BY Month ORDER by month

1 if Monday is the first day of the week, then (lowercase) date_format (DATE, '%x%v ')

2 If Sunday is the first day of the week, then (uppercase) Date_format (DATE, '%x%V ')

Statistics per week

Select Date_format (date, '%x year-%v week ') as week,sum (money) as money from Finance_base where date_format (date, '%Y ') =2010 Group BY week;
Select ((' Recview ' paytime ') + month (' Recview '. ' Paytime ')) as ' Paytime ', sum (' recview '. ' Rent ') as ' rent ' From ' Recview ' GROUP by
((Year (' Recview '. ' Paytime ') * +) + month (' Recview ', ' paytime ')) Order by ((' Recview ', ' paytime ') + month (' Rec View '. ' Paytime '));

Articles you may be interested in:
Mysql by year, quarter, month, week, day statistics query example
MySQL SQL statement by annual, quarterly, Monthly, weekly, and daily statistics query
MySQL query: Last week, month, quarter, year and week, month, quarter, year

-----------------------------------------------------------------------------------

from:http://phl.iteye.com/blog/717872

Table finance has two fields as follows
Date Date
Money Double (15,2)
The following need for the 2010 financial data of the table finance, monthly statistics

SQL code

1. select date_format (date,< Span class= "string" > '%y-%m ')  as month,sum (money)  as money   
2. from finance   
3. where date_format (date, '%Y ') =2010    
4. group by MONTH&NBSP;&NBSP;&NBSP;
5. order by MONTH&NBSP;&NBSP;

Here are the weekly statistics
View MySQL's Manual
%x year for the week where Sunday are the first day of the week, numeric, and four digits; Used with%V
%x year for the week, where Monday are the first day of the week, numeric, and four digits; Used with%v
which
1 if Monday is the first day of the week, then (lowercase) date_format (DATE, '%x%v ')
2 If Sunday is the first day of the week, then (uppercase) Date_format (DATE, '%x%V ')

SQL code

1. Select Date_format (DATE,'%x year-%v week ') as week,sum (money) as Money
2. From Finance_base
3. where Date_format (DATE,'%Y ') =2010
4. GROUP BY Week
   

Implementing MySQL Monthly Statistics tutorial