You need to add the columns you wish to group on and use
Count as aggregate function on
tblAssets.AssetID. Enable grouping for all other columns in your report. Not sure how you define departments in your database. Departments could come from the asset database itself or from AD users. The following uses the department field from the Lansweeper database.
Select Top 1000000 tblAssetCustom.Department,
tblOperatingsystem.Caption As OS,
Count(tblAssets.AssetID) As Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Group By tblAssetCustom.Department,
tblOperatingsystem.Caption
Order By tblAssetCustom.Department,
OS