In MySQL, month and day group groups and a statistical sort order

In the database we often use SQL statements to do a simple statistical sort of table, for the Date field, we can simply order it. For a complex point can be group,order by date, month, day, respectively.

Group by year, sorting by statistics:

SELECT Yera (Established_time), COUNT (*) as num <span style= "FONT-SIZE:13.75PX; Font-family:arial, Helvetica, Sans-serif; " >from ' Table_record ' GROUP by year (Established_time) </span>

Group by month, sorted by statistics:

Method One: Simple and practical

SELECT year (established_time) as "year", MONTH (established_time) as ' MONTH ', COUNT (*) as ' count ' from 
' Table_record '   
GROUP by year (established_time) DESC, MONTH (established_time)

Effect:

Year Month count

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

2014 1 8320

2014 2 5837

2014 3 25069

2014 4 29820

2014 5 25060

2014 6 17615

2014 7 1

2013 1 9114

2013 2 4258

Method Two: Use MySQL built-in string connection function CONCAT (str1, str2, ..., STR3).

SELECT  CONCAT (established_time), ', ', MONTH (established_time) as Data_time, COUNT (*) as num from   
' Table_ Record '   
#group by Data_time  
Group by year (Established_time) Desc,month (established_time)

This column more highlights: http://www.bianceng.cn/database/MySQL/

Effect:

Data_time num

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

2014,1 8320

2014,2 5837

2014,3 25069

2014,4 29910

2014,5 25018

2014,6 17347

2014,7 1

2013,1 9114

2013,2 4258

2013,3 8047

Note that this is not used here:

SELECT year (established_time) +month (established_time) as Data_time

This is wrong, it will cause 2010+1=2011 such a mistake.

In addition, if only use the following statement, is the statistics of the monthly data for many years.

SELECT  MONTH (established_time) as ' MONTH ', COUNT (*) as ' count ' from 
' tb_gongshangju_record_beijing ' GROUP by< C3/>month (established_time) DESC

The effect is as follows:

Month Count

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

12 44952

11 49720

10 38587

9 48967

8 52874

7 54082

6 69532

5 76999

4 87289

3 85249

2 39997

1 49017

(NULL) 34456

Group by date Day, sorting by statistics

SELECT year (established_time) as "year", MONTH (established_time) as ' MONTH ', Day (established_time) as ' Day ', COUNT (*) as ' Count ' from 
' Table_record '   
WHERE table_record ' established_time ' >= ' 2014-01-01 ' GROUP by year
( Established_time) DESC, MONTH (established_time) DESC, Day (established_time) DESC