Query with COUNT() and GROUP BY

Question

DATA

ID-USER-ROLE-PERM
1   1   1   1
2   1   1   2
3   1   1   3
4   1   1   4
5   1   1   5
6   1   1   6
12  1   1   7
13  1   1   8
9   1   3   1
10  1   3   5
11  1   4   1
14  13  1   8

Query description: How many users take value 1 as ROLE?

Result should be: 2

because only user number 1 and 13 takes value 1 as ROLE

This is my faulty query which returns 9:

SELECT COUNT(*) FROM assigned_roles WHERE ROLE = 1 GROUP BY ROLE

Any idea how to return 2 instead?

Answer 1

To count the number of different users use COUNT(DISTINCT USER).

Also you don't need GROUP BY ROLE as your WHERE clause ensures that there can only be a maximum of one group.

SELECT COUNT(DISTINCT USER) 
FROM assigned_roles 
WHERE ROLE = 1 

SQL Fiddle