SQL query, count users per month (datetime)

Question

I have a SQL table that i would like to change to the following format:

I already have that table, but I wasn't able to make it count the users from another table and insert them into the column.

When you say you "weren't able", what do you mean? What did you try, and how did it fail? — Blorgbeard, Feb 11 '16 at 19:47
What DBMS are you using? SQL Server? MySQL? Oracle? Something else? — Siyual, Feb 11 '16 at 19:48
I think you should (have) look(ed) here: http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement — Jorick Spitzen, Feb 11 '16 at 19:52

Fuzzy · Answer 1 · 2016-02-11T20:18:23.623

0

This should do it if you are using tSQL, but what about same months in different years?

;WITH CTE AS (
SELECT  DATENAME(month,SignUpTime) AS [MonthName],
       COUNT(UserID) AS [CountUsers] 
FROM Tbl_Users 
GROUP BY DATENAME(month,SignUpTime))

UPDATE A 
SET A.[CountUsers]=B.[CountUsers]
FROM Tbl_CountUsersPerMonth AS A LEFT OUTER JOIN 
    CTE AS B ON A.[MonthName]=B.[MonthName]

edited Feb 11 '16 at 20:18

answered Feb 11 '16 at 19:56

Fuzzy

3,810
2
15
33

my Database is SQL Server. – maorw Feb 11 '16 at 21:31

score 0 · Accepted Answer · answered Feb 12 '16 at 07:48

I suggest you instead of table use view like this:

CREATE VIEW vw_CountUsersPerMonth 
AS

SELECT YEAR(SignUpTime) as [Year],
        DATENAME(month,SignUpTime) AS [MonthName],
        COUNT(UserID) AS [CountUsers] 
FROM Tbl_Users 
GROUP BY YEAR(SignUpTime), DATENAME(month,SignUpTime))

SQL query, count users per month (datetime)

2 Answers2