I am building a chat site and for presenting the chats to the audiences, I have 3 Dropdownlists - Sports (Default is All Sports), Day/Month/Year, Users online/Total Users.
Now, if by default all sports is selected and I pick 1 month and Total users, the expected result should be
My query is
SELECT DISTINCT roo.[Sports],
roo.[Name],
COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers,
COUNT(DISTINCT liu.[LoggedInUserID]) AS UserOnline
FROM Room AS roo
LEFT JOIN LoggedInUser AS liu ON roo.RoomID = liu.RoomID
LEFT JOIN ChatUserLog AS chu ON roo.RoomID = chu.RoomID
AND chu.LoggedInTime >= DATEADD(DAY,-30,GETDATE())
GROUP BY roo.[Sports], roo.[Name]
ORDER BY TotalUsers DESC
One person suggested that with my method I am actually multiplying the row because of the two joins & so I need to aggregate first, then join.
So in the end, I tried this query too
with agg_ChatUserLog as (select RoomId, count(*) as cnt_user_tot from ChatUserLog WHERE LoggedInTime >= DATEADD(DAY,-30,GETDATE()) group by RoomId),
agg_LoggedInUser as (select RoomId, count(*) as cnt_user_logged from LoggedInUser group by RoomId)
select Sports, Name, cnt_user_tot, cnt_user_logged from Room r
left outer join agg_ChatUserLog acu on acu.RoomId = r.RoomId
left outer join agg_LoggedInUser alu on alu.RoomId = r.RoomId;
But this is also multiplying the results.
Where am I making the mistake in both the query?
Thanks in advance and have a nice day ahead.
What I have tried:
I guess, the issue is with "GROUP BY" items too... So, I am trying to modify and try them out too in
with agg_ChatUserLog as (select RoomId, count(*) as cnt_user_tot from ChatUserLog WHERE LoggedInTime >= DATEADD(DAY,-30,GETDATE()) group by RoomId), agg_LoggedInUser as (select RoomId, count(*) as cnt_user_logged from LoggedInUser group by RoomId) select Sports, Name, cnt_user_tot, cnt_user_logged from Room r left outer join agg_ChatUserLog acu on acu.RoomId = r.RoomId left outer join agg_LoggedInUser alu on alu.RoomId = r.RoomId GROUP BY cnt_user_tot ORDER BY cnt_user_tot DESC