SQL: Selecting distinct values for another column

Question

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!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

SQL: Selecting distinct values for another column

Sign up