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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0