Mysql – Conditional Count/Sum in Select


I've been trying differnt ways of approaching this, but I'm not having to much luck.

Basically this query returns all the directories an employee is built into. I need to add a field to the select of this query that lists how many other users with 'admin' access (u.access_level) are built into the directory).

Table:  Directories (has many users)
Table:  Users (users of the directory with different access levels)

SELECT,, u.employee_number, u.access_level, 'user_id', 
(** count of all users of this directory where their u.access_level = 'admin) AS admins 
FROM directories d JOIN users u ON = u.directory_id 
WHERE u.employee_number = '045283' 

So, I need to return a recordset of all directories an employee is built into, including the total number of users that have 'admin' access to each directory. Basically I need a count for some later framework logic like 'if the employee is the last admin of this directory – don't allow them to delete themselves or change their access level – upgrade another user to admin level first).

It feels like I'm needing a sub-query, but that's a little beyond my SQL skills right now. Thanks for any help!

Best Solution

sum(if(u.access_level = 'admin',1,0)) as admins

edit. Please note that this condition can be rewritten even in this way

sum(u.access_level = 'admin')

because mysql simply returns 1 if condition is true or 0 if it's not.

Related Question