Mysql performs statistical sorting by month as the group _ MySQL

Mysql performs statistical sorting by month. in the database, we often use SQL statements to perform simple statistical sorting on tables. for date fields, we can simply order them. For complex operations, you can perform group and order by year, month, and day of the date.

Group by year, statistical sorting:

SELECT YERA(established_time),COUNT(*) AS num FROM `table_record` GROUP BY YEAR (established_time)

Group by month, statistical sorting:

Method 1: 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 29910
2014 5 25018
2014 6 17347
2014 7 1
2013 1 9114
2013 2 4258
Method 2: Use the MySql built-in string connection function CONCAT (str1, str2,..., str3 ).
SELECTCONCAT(YEAR(established_time),',',MONTH( established_time)) AS data_time ,COUNT(*) AS num FROM `table_record` #group by data_timeGROUP BY YEAR(established_time) DESC,MONTH( established_time)
Effect:
Data_time num
-----------------
8320
5837
Copyright 3 25069
Copyright 4 29910
Copyright 5 25018
17347
Copyright 7 1
9114
4258
Note:
SELECTYEAR(established_time)+MONTH( established_time) as data_time 
This is incorrect. it may cause 2010 + 1 = 2011 errors.
In addition, if you only use the following statement, the monthly data is collected over the years.
SELECTMONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM `tb_gongshangju_record_beijing` GROUP BYMONTH(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
Group by date day, statistical sorting
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