SQL number of distinct values for the value in another column-CodePudding

Let's say I have a table with user logins:

create table usr_logins
(
    id int primary key, 
    logint_date date,
    user_name text,
    os_ver int
);

insert into usr_logins
values (1, '2018-12-23', 'Jack', 10)
      ,(2, '2018-12-24', 'Sam', 11)
      ,(3, '2018-12-24', 'Jack', 10)
      ,(4, '2018-12-24', 'Ann', 10)
      ,(5, '2018-12-25', 'Sam', 10)
      ,(6, '2019-12-26', 'Sam', 10)

I need to get a list of user names with a number of different OS versions.

Note that only Sam has logins from os_ver 10 and 11.

This is what I need:

CodePudding user response：

Since you need to get all usernames and corresponding distinct os_version count displayed (as os_num), you can try the following:

select user_name, count(DISTINCT os_ver) as os_num from usr_logins group by user_name

CodePudding user response：

It's not clear if you are expecting only user Sam or not since you've highlighted in red, but if so you can do

select user_name, Count(distinct os_ver) os_num
from usr_logins
group by User_Name
having  Count(distinct os_ver)>1

CodePudding user response：

select user_name, count(os_ver) osCount from usr_logins group by user_name having osCount > 1

Hope I understood you question correctly