Try this...
SELECT a.AssetStatusId, s.AssetStatus, COUNT(*) as [Assets] from TblAssetStatus s
left join TblAsset a on a.AssetStatusId = s.AssetStatusId
INNER JOIN dbo.TblStoreStock ON a.StoreStockId = dbo.TblStoreStock.StoreStockId
INNER JOIN dbo.TblStock ON dbo.TblStoreStock.StockId = dbo.TblStock.StockId
INNER JOIN dbo.TblItem ON dbo.TblStock.ItemId = dbo.TblItem.ItemId
INNER JOIN dbo.TblModel ON dbo.TblItem.ModelId = dbo.TblModel.ModelId
INNER JOIN dbo.TblIemType ON dbo.TblModel.ItemTypeId = dbo.TblIemType.ItemTypeId
Left JOIN dbo.TblCategory ON dbo.TblIemType.CategoryId = dbo.TblCategory.CategoryId
group by a.AssetStatusId, s.AssetStatus
Happy Coding!
:)