Sql-server – Count Assets Barcodes and Group by Rooms

countgroup byjoin;sql server

I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room.

I want the output to be the list of Asset Barcodes, Description,RoomNo and count for assets by room.

for example Room R0001 has 4 assets (01780,01781,01782,01783).

http://sqlfiddle.com/#!18/c2cb3/2

Best Answer

This does Query uses XML string concatenation to get the result in the format you need.

If AssetID or Description can be NULL then you would have to add additional logic

SELECT  '('+reverse(stuff(reverse(( 
        SELECT A.[AssetID] +',' 
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
        , 
        '('+reverse(stuff(reverse(( 
        SELECT A.[Description] +',' 
        FROM [dbo].[AssetRegister] A
        WHERE A.LocationRoomID = AR.LocationRoomID
        FOR XML PATH(''))), 1, 2,''))+')'
      ,LR.[RoomNo]
      ,COUNT(*)
FROM [dbo].[AssetRegister] AR
INNER JOIN [dbo].[LocationRoom] LR
on AR.LocationRoomID = LR.LocationRoomID
GROUP BY LR.[RoomNo],AR.LocationRoomID;

Result enter image description here

DB<>Fiddle

SQLFiddle

Edit:

Thank you very much is it possible to get all the 19 rows each one with their AssetBarcode 01780, Dell Computer, R001, 4

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

Result

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
...*(19 rows)*

DB<>Fiddle

Related Question