Mysql, respectively, by month, day for group, statistics sort order

In the database we often use SQL statements to simple statistical sorting of tables, for date fields, we can simply order them. For the complex point can be group,order by the date of the year, month, day, respectively.

GROUP by year, sort by statistic:

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, sort by statistic:

Method One: Simple and practical

SELECT year (established_time) as "year", Month (established_time) as ' month ', COUNT (*) as ' count ' from ' Table_record ' GRO Up 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 (Year (established_time), ', ', MONTH (Established_time)) as Data_time, COUNT (*) as num from ' Table_ Record ' #group by Data_timegroup-year (established_time) Desc,month (established_time)

Effect:

data_time     num   
---- ----- -------- ,
2014,1         8320 
2014,2         5837&NB Sp
2014,3        25069
2014,4        29910
2014,5       &NBS p;25018
2014,6        17347
2014,7            1   
2 013,1         9114
2013,2         4258
2013,3         80

Note that this is not used:

SELECT  

This is wrong and it will cause 2010+1=2011 of such errors.

In addition, if you use the following statement only, it is statistical data for many years.

SELECT  Month (established_time) as ' MONTH ', COUNT (*) as ' count ' from ' tb_gongshangju_record_beijing ' GROUP  by MONTH (established_time) DESC

The effect is as follows:

 month   Count  
------ --------
        44952
        497
        38587
     9     48967
     8   &N Bsp 52874
     7     54082
     6     69532
     5 & nbsp   76999
     4     87289
     3     85249
    &N Bsp;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