How to realize grouping Statistical query in SQLserver

How to implement grouping statistical query in SQLserver? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Set the AccessCount field to accumulate on the AccessCount if it is the same IP access within a specific time frame according to the requirements.

Create table Counter (CounterID int identity (1) not null, IP varchar (20), AccessDateTime datetime, AccessCount int)

The table is just a demonstration here, so it only provides the most basic fields. Now insert a few records into the table: insert into Counter select '127.0.0.1, 1 union all select' 127.0.0.2, 1 union all select '127.0.0.3, and query by year. In monthly time units, usually a simple grouping can copy the code as follows: select convert (varchar (7), AccessDateTime,120) as Date, sum (AccessCount) as [Count] from Counter group by convert (varchar (7), AccessDateTime,120)

Months that are not recorded after grouping like this will not be displayed as follows: of course, this is not what we want, so we have to implement it in a different way. The copy code is as follows: declare @ Year int set @ Year=2009 select m as [Date], sum (case when datepart (month,AccessDateTime) = m then AccessCount else 0 end) as [Count] from Counter c, (select 1 m union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) aa where @ Year=year (AccessDateTime) group by m

2 inquire according to the day, in hours. This is similar to the above, and the code is as follows

Declare @ DateTime datetime set @ DateTime=getdate () select right +': 00->'+ right +': 00'as DateSpan, sum (case when datepart (hour,AccessDateTime) > = an and datepart (hour,AccessDateTime)