Count records for every month in a year VS Where Between not giving the same number?

Hi,

I want to count how many logins by month. If i use a group by VS Where Between it no giving me the same number per month.

Should both query return the same number for September?

Ex: for the month September

SELECT COUNT(*) as [09]
FROM UserExc.tblLoginStats as tl
INNER JOIN UserExc.tblUser as tu ON tu.Username = tl.Username
where LoginDate BETWEEN '2018-09-01' AND '2018-09-30'

Result: 2947

For all the month of the year with the query below the month of September give me 2977

SELECT YEAR(LoginDate) as strYear, MONTH(LoginDate) as strMonth, COUNT(*) AS TOTALCOUNT
FROM UserExc.tblLoginStats as tl
INNER JOIN UserExc.tblUser as tu ON tu.Username = tl.Username
GROUP BY YEAR(LoginDate), MONTH(LoginDate)

|strYear|strMonth|TOTALCOUNT|
|2018|9|2977|
|2018|6|1439|
|2018|7|191|
|2018|10|3347|
|2018|11|1625|
|2018|5|1468|
|2018|8|1077|

Is LoginDate a date only or does it include time. If it includes time, you need to do this in the first query:

SELECT COUNT(*) as [09]
FROM UserExc.tblLoginStats as tl
INNER JOIN UserExc.tblUser as tu ON tu.Username = tl.Username
WHERE LoginDate >= '20180901' AND LoginDate < '20181001'

1 Like

Your query works tks
This will give me the same number.

Tks

SELECT COUNT(*) as [09]
FROM UserExc.tblLoginStats as tl
INNER JOIN UserExc.tblUser as tu ON tu.Username = tl.Username
WHERE LoginDate >= '20180901' AND LoginDate < '20181001'