MYSQL query grouped by date statistics

Table of Contents

    • 1. Group by day
      • 1.1, dayofyear, dayofmonth, dayofweek
      • 1.2, format function
    • 2. Group by week
      • 2.1, week function
      • 2.2, weekofyear function
      • 2.3, weekday function
      • 2.4, dayname function
    • 3. Group by month
      • 3.1, month function
      • 3.2, DATE_FORMAT function
    • 4. Group by season
      • 4.1, quarter function
    • 5. Group by year
      • 5.1, year function
      • 5.2, DATE_FORMAT function
    • epilogue

1. Group by day

1.1, dayofyear, dayofmonth, dayofweek

  • dayofyear(date) The function returns the day of the year in which the date is located, and the range is 1 ~ 366
  • dayofmonth(date) The function returns the day of the month in which the date is located, and the range is 1 ~ 31
  • dayofweek(date) The function returns the day of the week in which the date is located, and the range is 1 ~ 7

Query statement

select
dayofmonth(transtime) as transDay,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59'
group by transDay
order by transDay asc;

Query results

 + ---------- + ---------- +
| transDay | transNum |
 + ---------- + ---------- +
| 1 | 1704 |
| 2 | 985 |
| 3 | 723 |
| 4 | 606 |
| 5 | 581 |
| 6 | 1051 |
| 7 | 1257 |
| 8 | 637 |
| 9 | 1049 |
| 10 | 559 |
| 11 | 724 |
| 12 | 964 |
| 13 | 1139 |
| 14 | 2542 |
| 15 | 5957 |
| 16 | 3185 |
| 17 | 543 |
| 18 | 507 |
| 19 | 854 |
| 20 | 849 |
| 21 | 2216 |
| 22 | 3788 |
| 23 | 2498 |
| 24 | 693 |
| 25 | 597 |
| 26 | 756 |
| 27 | 854 |
| 28 | 1583 |
| 29 | 2180 |
| 30 | 1855 |
| 31 | 744 |
 + ---------- + ---------- +
31 rows in set (0.05 sec)

It should be noted that if it is dayofmonth or dayofweek The time spans months or weeks, and the data of multiple months or weeks will be merged together. If you want to separate, you can use the following formatting method.

1.2, format function

  • DATE_FORMAT(date, ‘%Y-%m-%d’) The function returns the formatted date according to the specified expression, including the year, month and day

Query statement

select
DATE_FORMAT(transtime, '%Y-%m-%d') as transDay,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59'
group by transDay
order by transDay asc;

Query results

 + ------------ + ---------- +
| transDay | transNum |
 + ------------ + ---------- +
| 2016-10-01 | 1704 |
| 2016-10-02 | 985 |
| 2016-10-03 | 723 |
| 2016-10-04 | 606 |
| 2016-10-05 | 581 |
| 2016-10-06 | 1051 |
| 2016-10-07 | 1257 |
| 2016-10-08 | 637 |
| 2016-10-09 | 1049 |
| 2016-10-10 | 559 |
| 2016-10-11 | 724 |
| 2016-10-12 | 964 |
| 2016-10-13 | 1139 |
| 2016-10-14 | 2542 |
| 2016-10-15 | 5957 |
| 2016-10-16 | 3185 |
| 2016-10-17 | 543 |
| 2016-10-18 | 507 |
| 2016-10-19 | 854 |
| 2016-10-20 | 849 |
| 2016-10-21 | 2216 |
| 2016-10-22 | 3788 |
| 2016-10-23 | 2498 |
| 2016-10-24 | 693 |
| 2016-10-25 | 597 |
| 2016-10-26 | 756 |
| 2016-10-27 | 854 |
| 2016-10-28 | 1583 |
| 2016-10-29 | 2180 |
| 2016-10-30 | 1855 |
| 2016-10-31 | 744 |
 + ------------ + ---------- +
31 rows in set (0.08 sec)

2. Group by week

2.1, week function

  • week(date) The function returns the date which is the week of the year, every week starts from Sunday, and the value range is 0 ~ 53

Query statement

select
week(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'
group by transWeek
order by transWeek asc;

Query results

 + ----------- + ---------- +
| transWeek | transNum |
 + ----------- + ---------- +
| 0 | 2 |
| 1 | 8 |
| 2 | 9 |
| 3 | 1 |
| 4 | 4 |
| 5 | 7 |
| 6 | 4 |
| 7 | 9 |
| 8 | 7 |
| 9 | 2 |
| 10 | 21 |
| 11 | 18 |
| 12 | 19 |
| 13 | 34 |
| 14 | 31 |
| 15 | 17 |
| 16 | 130 |
| 17 | 261 |
| 18 | 230 |
| 19 | 494 |
| 20 | 452 |
| 21 | 485 |
| 22 | 590 |
| 23 | 684 |
| 24 | 580 |
| 25 | 620 |
| 26 | 370 |
| 27 | 155 |
| 28 | 721 |
| 29 | 747 |
| 30 | 659 |
| 31 | 775 |
| 32 | 843 |
| 33 | 897 |
| 34 | 926 |
| 35 | 975 |
| 36 | 975 |
| 37 | 1048 |
| 38 | 393 |
| 39 | 4145 |
| 40 | 5840 |
| 41 | 12934 |
| 42 | 11942 |
| 43 | 9161 |
| 44 | 9102 |
| 45 | 8284 |
| 46 | 6150 |
| 47 | 5825 |
| 48 | 6374 |
| 49 | 6929 |
| 50 | 4366 |
| 51 | 3858 |
| 52 | 5855 |
 + ----------- + ---------- +
53 rows in set (0.12 sec)

2.2, weekofyear function

  • weekofyear(date) The function returns the date of the week of the year, each week starts from Monday, and the value range is 1 ~ 53

Query statement

select
weekofyear(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'
group by transWeek
order by transWeek asc;

Query results

 + ----------- + ---------- +
| transWeek | transNum |
 + ----------- + ---------- +
| 1 | 11 |
| 2 | 6 |
| 4 | 5 |
| 5 | 6 |
| 6 | 5 |
| 7 | 9 |
| 8 | 7 |
| 9 | 1 |
| 10 | 22 |
| 11 | 17 |
| 12 | 23 |
| 13 | 34 |
| 14 | 28 |
| 15 | 16 |
| 16 | 199 |
| 17 | 194 |
| 18 | 299 |
| 19 | 510 |
| 20 | 458 |
| 21 | 492 |
| 22 | 571 |
| 23 | 709 |
| 24 | 546 |
| 25 | 640 |
| 26 | 278 |
| 27 | 257 |
| 28 | 723 |
| 29 | 720 |
| 30 | 710 |
| 31 | 786 |
| 32 | 813 |
| 33 | 921 |
| 34 | 957 |
| 35 | 1002 |
| 36 | 932 |
| 37 | 1032 |
| 38 | 327 |
| 39 | 5064 |
| 40 | 5904 |
| 41 | 15070 |
| 42 | 11255 |
| 43 | 8518 |
| 44 | 9203 |
| 45 | 7836 |
| 46 | 5448 |
| 47 | 6608 |
| 48 | 5934 |
| 49 | 6639 |
| 50 | 4160 |
| 51 | 3887 |
| 52 | 5173 |
| 53 | 3 |
 + ----------- + ---------- +
52 rows in set (0.12 sec)

2.3, weekday function

  • weekday(date) Returns the day of the week, note that Monday is 0 and Tuesday is 1. . . Sunday is 6

Query statement

select
weekday(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59'
group by transWeek
order by transWeek asc;

Query results

 + ----------- + ---------- +
| transWeek | transNum |
 + ----------- + ---------- +
| 0 | 3262 |
| 1 | 2434 |
| 2 | 3155 |
| 3 | 3893 |
| 4 | 7598 |
| 5 | 14266 |
| 6 | 9572 |
 + ----------- + ---------- +
7 rows in set (0.05 sec)

At the same time, if you feel unintuitive and you want to get the name, you can use dayname(date) instead of weekday(date) , the result is as follows

2.4, dayname function

  • dayname(date) Returns the day of the week: MONDAY, TUESDAY…SUNDAY

Query statement

select
dayname(transtime) as transWeek,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59'
group by transWeek
order by transWeek asc;

Query results

 + ----------- + ---------- +
| transWeek | transNum |
 + ----------- + ---------- +
| Friday | 7598 |
| Monday | 3262 |
| Saturday | 14266 |
| Sunday | 9572 |
| Thursday | 3893 |
| Tuesday | 2434 |
| Wednesday | 3155 |
 + ----------- + ---------- +
7 rows in set (0.08 sec)

3. Group by month

3.1, month function

  • month(date) The function returns the month corresponding to the date, ranging from 1 to 12

Query statement

select
month(transtime) as transMonth,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'
group by transMonth
order by transMonth asc;

Query results

 + ------------ + ---------- +
| transMonth | transNum |
 + ------------ + ---------- +
| 1 | 25 |
| 2 | 27 |
| 3 | 83 |
| 4 | 449 |
| 5 | 1893 |
| 6 | 2611 |
| 7 | 2411 |
| 8 | 3811 |
| 9 | 5334 |
| 10 | 44180 |
| 11 | 30140 |
| 12 | 24004 |
 + ------------ + ---------- +
12 rows in set (0.12 sec)

At the same time, if you want to get the name, you can use MONTHNAME(date) instead of month(date) , the result is as follows:

 + ------------ + ---------- +
| transMonth | transNum |
 + ------------ + ---------- +
| April | 449 |
| August | 3811 |
| December | 24004 |
| February | 27 |
| January | 25 |
| July | 2411 |
| June | 2611 |
| March | 83 |
| May | 1893 |
| November | 30140 |
| October | 44180 |
| September | 5334 |
 + ------------ + ---------- +
12 rows in set (0.20 sec)

It should be noted that if it is a new year, the data of many years will be merged together, and it is not known how much a certain month is in each year. If you want to separate, you can use the following The formatting method.

3.2, DATE_FORMAT function

  • DATE_FORMAT(date, ‘%Y-%m’) The function returns the formatted date according to the specified expression, including the year and month

Query statement

select
DATE_FORMAT(transtime, '%Y-%m') as transMonth,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'
group by transMonth
order by transMonth asc;

Query results

 + ------------ + ---------- +
| transMonth | transNum |
 + ------------ + ---------- +
| 2016-01 | 25 |
| 2016-02 | 27 |
| 2016-03 | 83 |
| 2016-04 | 449 |
| 2016-05 | 1893 |
| 2016-06 | 2611 |
| 2016-07 | 2411 |
| 2016-08 | 3811 |
| 2016-09 | 5334 |
| 2016-10 | 44180 |
| 2016-11 | 30140 |
| 2016-12 | 24004 |
 + ------------ + ---------- +
12 rows in set (0.20 sec)

4. Group by season

4.1, quarter function

  • quarter(date) The function returns the quarter corresponding to the date, ranging from 1 to 4

Query statement

select
quarter(transtime) as quarterNo,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59'
group by quarterNo
order by quarterNo asc;

Query results

 + ----------- + ---------- +
| quarterNo | transNum |
 + ----------- + ---------- +
| 1 | 135 |
| 2 | 4953 |
| 3 | 11556 |
| 4 | 98324 |
 + ----------- + ---------- +
4 rows in set (0.12 sec)

5. Group by year

5.1, year function

  • year(date) The function returns the year corresponding to the date

Query statement

select
year(transtime) as transYear,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2015-01-01 00:00:00' and '2022-12-31 23:59:59'
group by transYear
order by transYear asc;

Query results

 + ----------- + ---------- +
| transYear | transNum |
 + ----------- + ---------- +
| 2015 | 6 |
| 2016 | 114968 |
| 2017 | 66703 |
| 2018 | 2738 |
| 2019 | 1853 |
| 2020 | 651 |
| 2021 | 40 |
 + ----------- + ---------- +
7 rows in set (0.19 sec)

5.2, DATE_FORMAT function

  • DATE_FORMAT(date, ‘%Y’) The function returns the formatted date according to the specified expression, including the year

Query statement

select
DATE_FORMAT(transtime, '%Y') as transYear,
count(*) as transNum
from tb_inf_otherbiz
where transtime between '2015-01-01 00:00:00' and '2022-12-31 23:59:59'
group by transYear
order by transYear asc;

Query results

 + ----------- + ---------- +
| transYear | transNum |
 + ----------- + ---------- +
| 2015 | 6 |
| 2016 | 114968 |
| 2017 | 66703 |
| 2018 | 2738 |
| 2019 | 1853 |
| 2020 | 651 |
| 2021 | 40 |
 + ----------- + ---------- +
7 rows in set (0.19 sec)

Conclusion

The operations in this article are all based on the mysql8.0 version. Understanding the functions of mysql will make these queries easier.