1

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

  • So do you want comma separated lists of the assets, Descriptions based on the LocationRoomID / RoomNo? – Randi Vertongen Mar 12 at 10:35
  • @RandiVertongen yes please i want to see all my 19 asset barcodes – Senyo Aborgah Mar 12 at 10:37
0

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

  • Thank you very much is it possible to get all the 19 rows each one with their AssetBarcode 01780, Dell Computer, R001, 4 – Senyo Aborgah Mar 12 at 11:01
  • @SenyoAborgah Edited it, like that? – Randi Vertongen Mar 12 at 12:10
  • 1
    that's 100% the output I wanted. So you used COUNT(*) OVER PARTITION BY RoomNo. Thank You very much – Senyo Aborgah Mar 13 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.