How to get a count of how often a name/string appears

Question

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,
  tblAssets.AssetName
Order By 'Besitzer',
  'Name'

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

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
('user1',1),
('user1',2),
('user2',3),
('user3',4),
('user4',5),
('user4',6)

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