Sql query for counting records per month

Question

I have this data set that I need to build for the number of visits per month for a specific user. I have a sql table that contains these fields:

• User nvarchar (30)
• DateVisit datetime

What I want to achieve now is to get all visits grouped by month for each user, something like an image:

I started the request, I can get months and the total amount of visits for this month (not shared by the user) with this request;

select [1] AS January, [2] AS Febrary, [3] AS March, [4] AS April, [5] AS May, [6] AS June, [7] AS July, [8] AS August, [9] AS September, [10] AS October, [11] AS November, [12] AS December from ( SELECT MONTH(DateVisit) AS month, [User] FROM UserVisit ) AS t PIVOT ( COUNT([User]) FOR month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) p 

With the request above, I get this result:

Now I want to know how I can add another column for the user and split the values ​​by the user. I know his Friday, but if someone can help me, it will be very grateful. Thanks in advance, Laziale

Answer 1

Ok, both solutions look good. Ali's answer works, but instead I would use the SUM () function, I hate NULLS. Try both options and see query plans based on runtime.

I always create a test table with data so that I don't give the user, Aziale, bad answers.

The code below is not the most beautiful, but it sets up a test case. I created a database in tempdb called user_visits. For each month, I used a for loop to add users and gave them the start date for the month.

Now that we have the data, we can play.

 -- Drop the table drop table tempdb.dbo.user_visits go -- Create the table create table tempdb.dbo.user_visits ( uv_id int identity(1, 1), uv_visit_date smalldatetime, uv_user_name varchar(30) ); go -- January data declare @cnt int = 1; while @cnt <= 103 begin if (@cnt <= 21) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', 'Patrick'); if (@cnt <= 44) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', 'Barbara'); if (@cnt <= 65) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', 'Danielle'); if (@cnt <= 103) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130101', 'John'); set @cnt = @cnt + 1 end go -- February data declare @cnt int = 1; while @cnt <= 99 begin if (@cnt <= 29) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', 'Patrick'); if (@cnt <= 42) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', 'Barbara'); if (@cnt <= 55) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', 'Danielle'); if (@cnt <= 99) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130201', 'John'); set @cnt = @cnt + 1 end go -- March data declare @cnt int = 1; while @cnt <= 98 begin if (@cnt <= 25) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', 'Patrick'); if (@cnt <= 46) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', 'Barbara'); if (@cnt <= 75) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', 'Danielle'); if (@cnt <= 98) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130301', 'John'); set @cnt = @cnt + 1 end go -- April data declare @cnt int = 1; while @cnt <= 91 begin if (@cnt <= 32) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', 'Patrick'); if (@cnt <= 48) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', 'Barbara'); if (@cnt <= 60) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', 'Danielle'); if (@cnt <= 91) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130401', 'John'); set @cnt = @cnt + 1 end go -- May data declare @cnt int = 1; while @cnt <= 120 begin if (@cnt <= 40) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', 'Patrick'); if (@cnt <= 41) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', 'Barbara'); if (@cnt <= 70) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', 'Danielle'); if (@cnt <= 120) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130501', 'John'); set @cnt = @cnt + 1 end go -- June data declare @cnt int = 1; while @cnt <= 103 begin if (@cnt <= 17) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', 'Patrick'); if (@cnt <= 45) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', 'Barbara'); if (@cnt <= 62) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', 'Danielle'); if (@cnt <= 103) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130601', 'John'); set @cnt = @cnt + 1 end go -- July data declare @cnt int = 1; while @cnt <= 99 begin if (@cnt <= 20) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', 'Patrick'); if (@cnt <= 43) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', 'Barbara'); if (@cnt <= 66) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', 'Danielle'); if (@cnt <= 99) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130701', 'John'); set @cnt = @cnt + 1 end go -- August data declare @cnt int = 1; while @cnt <= 98 begin if (@cnt <= 26) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', 'Patrick'); if (@cnt <= 47) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', 'Barbara'); if (@cnt <= 71) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', 'Danielle'); if (@cnt <= 98) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130801', 'John'); set @cnt = @cnt + 1 end go -- September data declare @cnt int = 1; while @cnt <= 91 begin if (@cnt <= 25) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', 'Patrick'); if (@cnt <= 49) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', 'Barbara'); if (@cnt <= 59) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', 'Danielle'); if (@cnt <= 91) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20130901', 'John'); set @cnt = @cnt + 1 end go -- October data declare @cnt int = 1; while @cnt <= 120 begin if (@cnt <= 25) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', 'Patrick'); if (@cnt <= 40) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', 'Barbara'); if (@cnt <= 73) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', 'Danielle'); if (@cnt <= 120) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131001', 'John'); set @cnt = @cnt + 1 end go -- November data declare @cnt int = 1; while @cnt <= 101 begin if (@cnt <= 32) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', 'Patrick'); if (@cnt <= 50) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', 'Barbara'); if (@cnt <= 65) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', 'Danielle'); if (@cnt <= 101) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131101', 'John'); set @cnt = @cnt + 1 end go -- December data declare @cnt int = 1; while @cnt <= 90 begin if (@cnt <= 40) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'Patrick'); if (@cnt <= 52) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'Barbara'); if (@cnt <= 61) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'Danielle'); if (@cnt <= 90) insert into tempdb.dbo.user_visits (uv_visit_date, uv_user_name) values ('20131201', 'John'); set @cnt = @cnt + 1 end go 

Please do not use reserved encoding words as column names - IE - month is a backup word.

The code below gives the correct answer.

 -- Grab the data (1) select my_user, [1] AS January, [2] AS Febrary, [3] AS March, [4] AS April, [5] AS May, [6] AS June, [7] AS July, [8] AS August, [9] AS September, [10] AS October, [11] AS November, [12] AS December from ( SELECT MONTH(uv_visit_date) AS my_month, uv_user_name as my_user FROM tempdb.dbo.user_visits ) AS t PIVOT ( COUNT(my_month) FOR my_month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) as p 

 -- Grab the data (2) SELECT uv_user_name , SUM(CASE WHEN MONTH(uv_visit_date) = 1 THEN 1 ELSE 0 END) January , SUM(CASE WHEN MONTH(uv_visit_date) = 2 THEN 1 ELSE 0 END) Feburary , SUM(CASE WHEN MONTH(uv_visit_date) = 3 THEN 1 ELSE 0 END) March , SUM(CASE WHEN MONTH(uv_visit_date) = 4 THEN 1 ELSE 0 END) April , SUM(CASE WHEN MONTH(uv_visit_date) = 5 THEN 1 ELSE 0 END) May , SUM(CASE WHEN MONTH(uv_visit_date) = 6 THEN 1 ELSE 0 END) June , SUM(CASE WHEN MONTH(uv_visit_date) = 7 THEN 1 ELSE 0 END) July , SUM(CASE WHEN MONTH(uv_visit_date) = 8 THEN 1 ELSE 0 END) August , SUM(CASE WHEN MONTH(uv_visit_date) = 9 THEN 1 ELSE 0 END) September , SUM(CASE WHEN MONTH(uv_visit_date) = 10 THEN 1 ELSE 0 END) October , SUM(CASE WHEN MONTH(uv_visit_date) = 11 THEN 1 ELSE 0 END) November , SUM(CASE WHEN MONTH(uv_visit_date) = 12 THEN 1 ELSE 0 END) December FROM tempdb.dbo.user_visits GROUP BY uv_user_name 

When doing this type of analysis, always flush the cache / buffers and get I / O.

 -- Show time & i/o SET STATISTICS TIME ON SET STATISTICS IO ON GO -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO -- Solution 1 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 42 ms. (4 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'user_visits'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 5 ms. 

 -- Solution 2 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (4 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'user_visits'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 5 ms. 

Both solutions have the same number of readings, worksheet, etc. However, the SUM () solution has one smaller operator.

I am going to give to both people who answered thumbs +1!

Answer 2

You were almost there: just add the user to the selection list:

 select [Usr], [1] AS January, [2] AS Febrary, [3] AS March, [4] AS April, [5] AS May, [6] AS June, [7] AS July, [8] AS August, [9] AS September, [10] AS October, [11] AS November, [12] AS December from ( SELECT MONTH(DateVisit) AS month, [User], [User] as [Usr] FROM UserVisit ) AS t PIVOT ( COUNT([User]) FOR month IN([1], [2], [3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) p 

Answer 3

 SELECT [User] , COUNT(CASE WHEN MONTH(DateVisit) = 1 THEN 1 ELSE NULL END) January , COUNT(CASE WHEN MONTH(DateVisit) = 2 THEN 1 ELSE NULL END) Feburary , COUNT(CASE WHEN MONTH(DateVisit) = 3 THEN 1 ELSE NULL END) March , COUNT(CASE WHEN MONTH(DateVisit) = 4 THEN 1 ELSE NULL END) April , COUNT(CASE WHEN MONTH(DateVisit) = 5 THEN 1 ELSE NULL END) May , COUNT(CASE WHEN MONTH(DateVisit) = 6 THEN 1 ELSE NULL END) June , COUNT(CASE WHEN MONTH(DateVisit) = 7 THEN 1 ELSE NULL END) July , COUNT(CASE WHEN MONTH(DateVisit) = 8 THEN 1 ELSE NULL END) August , COUNT(CASE WHEN MONTH(DateVisit) = 9 THEN 1 ELSE NULL END) September , COUNT(CASE WHEN MONTH(DateVisit) = 10 THEN 1 ELSE NULL END) October , COUNT(CASE WHEN MONTH(DateVisit) = 11 THEN 1 ELSE NULL END) November , COUNT(CASE WHEN MONTH(DateVisit) = 12 THEN 1 ELSE NULL END) December FROM Table_Name GROUP BY [USER]