I have a database of devices owned by users, I want see how many users own more than 2 devices, but it would suffice to just get the total count.

Real query:

Select tblAssetUserRelations.Username As 'Besitzer',
  tblAssets.AssetName As 'Name'
From tblAssets
  Inner Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tsysAssetTypes.AssetTypename = 'Windows' Or
    tsysAssetTypes.AssetTypename = 'Apple Mac')
Group By tblAssetUserRelations.Username,
Order By 'Besitzer',

As an more simple example we can use those tables:

Username         DeviceID
user1            1
user1            2
user2            3
user3            4
user4            5
user4            6

DeviceID         DeviceName
1                NB001
2                DEV001
3                NB002
4                DEV002
5                NB003
6                DEV003

Expected Result:

Username         Count
user1            2
user2            1
user3            1
user4            2

Any idea how to get the expected result?

EDIT: wrong naming of column

1 Answer 1


Not sure what the rules are re 1 user owning the same device multiple times, if this should increase the count remove the distinct from within the count aggregate function

create table #x (Username  varchar(20),DeviceName int)
insert #x (username, devicename) values

select username, count(distinct devicename) as devcount 
from #x 
group by username

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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