I have coded a chatroom using asp.net, C# & MS SQL. I have two Dropdowns for selecting Sports and then another for Sorting the chatrooms based on Users online/total users.
When I select "All sports" (default value) and "Users online", the query works fine.
But when I select "All sports" (default value) and "total users", the query gives some error.
"SELECT roo.[Sports] As Sport, roo.[Name] AS ChatRoomName, COUNT(DISTINCT liu.[LoggedInUserID]) AS OnlineUsers, COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers 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]";
The results should be like this
Chatroom Name Online users Total Users
Football
Best football player ever? 0 5671
Messi or Ronaldo? 5 346
Who'll win EURO this year? 0 134
Swimming
Best ever Swimmer? 0 3343
Phelps or Spitz? 0 1056
Lochte or Phelps for gold? 0 45
Tennis
Serena or Steffi graf? 0 432
Federer or Djokovic? 0 298
Best server ever? 0 43
My results are not getting sorted properly as well as the sports having online users are shown twice too.
CREATE TABLE [dbo].[ChatUserLog] (
[ChatUserLogId] BIGINT IDENTITY (1, 1) NOT NULL,
[MId] INT NOT NULL,
[RoomId] INT NOT NULL,
[LoggedInTime] DATETIME DEFAULT (getdate()) NOT NULL,
[LogOutTime] DATETIME DEFAULT (getdate()) NULL,
PRIMARY KEY CLUSTERED ([ChatUserLogId] ASC)
);
CREATE TABLE [dbo].[LoggedInUser] (
[LoggedInUserID] INT IDENTITY (1, 1) NOT NULL,
[MId] INT NOT NULL,
[RoomID] INT NOT NULL,
[NickName] VARCHAR (50) NOT NULL,
[Icon] VARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_LoggedInUser] PRIMARY KEY CLUSTERED ([LoggedInUserID] ASC)
);
CREATE TABLE [dbo].[Message] (
[MessageID] INT IDENTITY (1, 1) NOT NULL,
[RoomID] INT NOT NULL,
[MId] INT NOT NULL,
[ToUserID] INT NULL,
[Text] VARCHAR (MAX) NOT NULL,
[TimeStamp] DATETIME DEFAULT (getdate()) NOT NULL,
[Color] VARCHAR (50) NULL,
[NickName] VARCHAR (50) NOT NULL,
[Icon] VARCHAR (MAX) NOT NULL,
PRIMARY KEY CLUSTERED ([MessageID] ASC)
);
CREATE TABLE [dbo].[Room] (
[RoomID] INT IDENTITY (6666, 1) NOT NULL,
[Name] VARCHAR (100) NOT NULL,
[Sports] VARCHAR (50) NOT NULL,
[CreatedDate] DATETIME DEFAULT (getdate()) NOT NULL,
[CreatedBy] VARCHAR (50) NOT NULL,
[CreatedUserID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([RoomID] ASC)
);
Thanks in advance and have a nice day ahead.
What I have tried:
I am trying
"SELECT roo.[Sports] As Sport, roo.[Name] AS ChatRoomName, COUNT(DISTINCT liu.[LoggedInUserID]) AS OnlineUsers, COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers 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]";