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 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 user开发者_JAVA百科s 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!

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.

try this :

SELECT d.id, d.name, u.employee_number, u.access_level, u.id 'user_id', 
SUM(IF(u.access_level = 'admin',1,0)) AS admins 
FROM directories d JOIN users u ON d.id = u.directory_id 
WHERE u.employee_number = '045283' 
ORDER BY d.NAME

this way the field will only add 1 when the u.access_level = 'admin' and if it's not then it will add 0 - which of course means it will not count it

Select d.id, d.name, u.employee_number, u.access_level, u.id 'user_id'
    , (Select Count(*)
        From directories As D1
            Join users As U1
                On U1.directory_id = D1.id
        Where U1.access_level = 'admin'
            And D1.id = D.id) As AdminUsers
From directories As D
    Join users As U
        On U.directory_id = D.id
Where u.employee_number = '045283'
Order By d.Name

Another solution using a derived table (which might perform better):

Select d.id, d.name, u.employee_number, u.access_level, u.id 'user_id'
    , Coalesce(AdminDir.AdminCount,0) As AdminCount
From directories As D
    Join users As U
        On U.directory_id = D.id
    Left Join   (
                Select D1.id, Count(*) As AdminCount
                From directories As D1
                    Join users As U1
                        On U1.directory_id = D1.id
                Where U1.access_level = 'admin'
                Group By D1.id  
                ) As AdminDir
            On AdminDir.id = D.id
Where u.employee_number = '045283'
Order By d.Name

Would you be looking for something like this:

SELECT d.id, d.name, u.employee_number, u.access_level, u.id 'user_id', 
SUM(IF(u.access_level = 'admin',1,0)) AS admins
FROM directories d JOIN users u ON d.id = u.directory_id 
WHERE u.employee_number = '045283' 
ORDER BY d.NAME