Conditional Count/Sum in Select

Question

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 d.id, d.name, u.employee_number, u.access_level, u.id 'user_id', 
(** count of all users of this directory where their u.access_level = 'admin) AS admins 
FROM directories d JOIN users u ON d.id = u.directory_id 
WHERE u.employee_number = '045283' 
ORDER BY d.NAME 

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!

Answer 1

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.