Aggregate function query

Question

SELECT

COUNT(DISTINCT(CONVERT(VARCHAR(10), Logintime, 101)))

AS COUNT

FROM

UserLoginStatus where UserId=15

HAVING DATEDIFF(MINUTE,MIN(Logintime),MAX(Logouttime))< 480

above i need to fetch Month(Logintime) in where condition .

if i keep in query it is showing empty otherwise it is showing count as 2

how to do that one

Ramesh Maruthi · Answer

SELECT COUNT(DISTINCT(CONVER(VARCHAR(10) Logintime 101))) AS COUNT FROM UserLoginStatus WHERE UserId=15 and MONTH (Logintime) =9 HAVING DATEDIFF(MINUTEMIN(Logintime)MAX(Logouttime))

Sasi Reddy · Answer

In my query i need to fetch Month(Logintime) in where condition how to do?.. i need to get data based on userid and based on month. so i have to write condition for month also how to write that one?.

Ramesh Maruthi · Answer

Hi Sasi If you not using where condition you said ur getting two count right  can u see whether that two count has userid 15 ? SELECT UserID COUNT(DISTINCT(CONVERT(VARCHAR(10) Logintime 101))) AS COUNT FROM UserLoginStatus Group By UserID HAVING DATEDIFF(MINUTEMIN(Logintime)MAX(Logouttime)) This gives result with userid so later you can query with that userid by using where condition Other way : SELECT COUNT(DISTINCT(CONVERT(VARCHAR(10) Logintime 101))) AS COUNT FROM UserLoginStatus Where UserID IN( Select UserID FROM UserLoginStatus Group By UserID HAVING DATEDIFF(MINUTEMIN(Logintime)MAX(Logouttime))