wra wra - 1 year ago 61
SQL Question

SQL: Selecting distinct values for another column

I'm trying to determine how many unique values from one column can be grouped by values in another column.

There are many columns in my sheet, but the ones I'm interested in are: 'department' and 'user'

How the data is that for these two columns, there are repeat users with the same department.

Eg.

Department User

Insights Mike

Insights Mike

Insights Chris

Market Julie

Research Will

Research Sabrina

Research Bryan


What I would want is the following:

Department DistinctUsers

Insights 2

Market 1

Research 3


My basic SQL knowledge tells me this is the structure of the code:

SELECT department, COUNT(DISTINCT user)
FROM Sheet1
GROUP BY department, user


I have seen that people would implement
(SELECT DISTINCT User from Sheet1)
in the
FROM
clause, but I have failed to integrate that into the code.

Any suggestions or tips is much appreciated!

Thanks!

Answer Source

As mentioned in the comments, you just need to remove users from the group by.

SELECT 
   department, 
   COUNT(DISTINCT [user]) as CT
FROM Sheet1
GROUP BY department

ACCESS

SELECT
   department,
   count([user])
FROM
   (SELECT DISTINCT department, [user] from Sheet1) as x
GROUP BY
   department
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download