Grouping statistics by time in SQL Server

The requirement raised by a group of friends is very simple, that is, grouping statistics.

One Table tb_visit

Guid visitdate visitcount

No. access times

The requirement is that the number of visits can be calculated by year, month, or year, and sorted by the number of visits, in the form of year-month visits/year visits

The SQL statement written to him later is as follows:

1. Statistics by year and month

Select convert (varchar (7), [visitdate], 120) as time, sum (visitcount) as access times
From tb_visit
Group by convert (varchar (7), [visitdate], 120) order by access times DESC;

2. Annual Statistics

Select convert (varchar (7), [visitdate], 120) as time, sum (visitcount) as access times
From tb_visit
Group by convert (varchar (4), [visitdate], 120) order by access times DESC;

Conclusion: The third parameter 120 of the convert function indicates that the time format follows: yyyy-mm-dd hh: mm: SS format, and all the first parameters are varchar (n) the length of N is determined by the number of digits.

7: yyyy-mm (year-month)

4: yyyy (year)