2 left joins (MSSQL) put together multiplying the results. But it shouldn't

See more:

SQL

SQL-Server

multi-binding

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