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