How to get a count of how often a name/string appears
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