SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[AssetRegister](
	[AssetRegisterID] [int] NOT NULL,
	[AssetID] [nvarchar](100) NULL,
	[Description] [nvarchar](max) NULL,
	[LocationRoomID] [int] NOT NULL
	);
CREATE TABLE [dbo].[LocationRoom](
	[LocationRoomID] [int] NOT NULL,
	[RoomNo] [nvarchar](max) NULL,
	[LocationDescription] [nvarchar](max) NULL
);
INSERT INTO [dbo].[AssetRegister]
           ([AssetRegisterID],[AssetID],[Description],[LocationRoomID])
VALUES   (1,'01780','DELL LAPTOP', 1),
         (2,'01781','DELL MONITOR', 1),
		 (3,'01782','DELL CPU', 1),
		 (4,'01783','DELL KEYBOARD', 1),
		 (5,'01784','HP LAPTOP', 2),
		 (6,'01785','HP MONITR', 2),
		 (7,'01786','HP CPU', 2),
		 (8,'01787','HP KEYBOARD', 2),
		 (9,'01788','APPLE LAPTOP AIR', 3),
		 (10,'01789','APPLE LAPTOP PRO', 3),
		 (11,'01790','APPLE MONITOR', 3),
		 (12,'01791','APPLE CPU', 3),
		 (13,'01792','APPLE KEYBOARD', 3),
		 (14,'01793','APPLE IPAD', 3),
		 (15,'01794','APPLE WATCH', 3),
		 (16,'01795','APPLE PHPNE', 3),
		 (17,'01796','IBM LAPTOP', 4),
		 (18,'01797','IBM LAPTOP 2', 4),
		 (19,'01798','IBM SERVER', 4);
19 rows affected

INSERT INTO [dbo].[LocationRoom]
           ([LocationRoomID],[RoomNo],[LocationDescription])
     VALUES
           (1,'R0001','IT Lab 1'),
		   (2,'R0002','IT Lab 2'),
		   (3,'R0003','IT Lab 3'),
		   (4,'R0004','IT Lab 4');
4 rows affected

SELECT  AR.AssetID, 
        AR.[Description],
        LR.[RoomNo],
        COUNT(*) OVER(PARTITION BY LR.RoomNo)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID


AssetID
Description
RoomNo
(No column name)


01780
DELL LAPTOP
R0001
4

01781
DELL MONITOR
R0001
4

01782
DELL CPU
R0001
4

01783
DELL KEYBOARD
R0001
4

01784
HP LAPTOP
R0002
4

01785
HP MONITR
R0002
4

01786
HP CPU
R0002
4

01787
HP KEYBOARD
R0002
4

01788
APPLE LAPTOP AIR
R0003
8

01789
APPLE LAPTOP PRO
R0003
8

01790
APPLE MONITOR
R0003
8

01791
APPLE CPU
R0003
8

01792
APPLE KEYBOARD
R0003
8

01793
APPLE IPAD
R0003
8

01794
APPLE WATCH
R0003
8

01795
APPLE PHPNE
R0003
8

01796
IBM LAPTOP
R0004
3

01797
IBM LAPTOP 2
R0004
3

01798
IBM SERVER
R0004
3

AssetID	Description	RoomNo	(No column name)
01780	DELL LAPTOP	R0001	4
01781	DELL MONITOR	R0001	4
01782	DELL CPU	R0001	4
01783	DELL KEYBOARD	R0001	4
01784	HP LAPTOP	R0002	4
01785	HP MONITR	R0002	4
01786	HP CPU	R0002	4
01787	HP KEYBOARD	R0002	4
01788	APPLE LAPTOP AIR	R0003	8
01789	APPLE LAPTOP PRO	R0003	8
01790	APPLE MONITOR	R0003	8
01791	APPLE CPU	R0003	8
01792	APPLE KEYBOARD	R0003	8
01793	APPLE IPAD	R0003	8
01794	APPLE WATCH	R0003	8
01795	APPLE PHPNE	R0003	8
01796	IBM LAPTOP	R0004	3
01797	IBM LAPTOP 2	R0004	3
01798	IBM SERVER	R0004	3