SQL grouping statistics query (grouping by month and hour)

Source: Internet
Author: User

First create a data table

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

This table is only used for demonstration, so only the most basic fields are provided.

IP address, access time, and number of visits. If you insert a record every time you access the database, the accesscount is not required. You can use count for queries. This puts a lot of pressure on the database when the traffic is large. You can set the accesscount field to accumulate access from the same IP address in a specific time range as required.

Insert several records into the table

insert into Counterselect '',getdate(),1 union allselect '',getdate(),1 union allselect '',getdate(),1

1. query by year, in the unit of month

Generally, a simple grouping can be done.

select     convert(varchar(7),AccessDateTime,120) as Date,    sum(AccessCount) as [Count]from    Counter group by     convert(varchar(7),AccessDateTime,120)

The month that is not recorded after grouping like this will not be displayed, as shown below:

This is of course not what we want, so we have to change the way we implement it, as follows:

declare @Year intset @Year=2009select     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    ) aawhere     @Year=year(AccessDateTime) group by       m

The query result is as follows:

2Query by day, in hours. This is similar to the above Code. The Code is as follows:

Declare @ datetime set @ datetime = getdate


Select right (100 + A, 2) + ': 00->' + right (100 + B, 2) + ': 00' as datespan, sum (case when datepart (hour, accessdatetime)> = A and datepart (hour, accessdatetime) <B then accesscount else 0 end) as [count] from Counter C, (select 0, 1 B Union all select 1, 2 Union all select 2, 3 Union all select 3, 4 union all select 4, 5 Union all select 5, 6 Union all select 6, 7 Union all select 7, 8 Union all Select 8, 9 Union all select 9, 10 Union all select 10, 11 union all select 11, 12 Union all select 12, 13 Union all select 13, 14 union all select 14, 15 Union all select 15, 16 Union all select 16, 17 Union all select 17, 18 Union all select 18, 19 Union all select 19,20 Union all select 20,21 Union all select 21,22 Union all select 22,23 Union all select 23,24) AA where datediff (day, @ datetime, accessdatetime) = 0 group by right (100 + A, 2) + ': 00->' + right (100 + B, 2) + ': 00'

The query result is as follows:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.