I like to list all companies in a table along with the number of admins that each specific company has.

The tables:

```
companies
id
name
...

users
id
company_id
...

groups ('id' = 1 has 'name' = admin)
id
name 

users_groups
id
user_id
group_id

```

To list all 'companies' I write this:

```
SELECT companies.name
FROM companies

```

To get the number of 'admin' in one specific 'company'(with a given id) I write this

```
SELECT COUNT (users.id) 
FROM users, companies, users_groups WHERE
users.company_id = companies.id AND 
users_groups.user_id = users.id AND
users_groups.group_id = 1

```

So how can I merge these two questions? This fails:

```
SELECT 
  companies.name, 
    (
      SELECT COUNT (users.id) 
      FROM users, companies, users_groups WHERE
      users.company_id = companies.id AND 
      users_groups.user_id = users.id AND
      users_groups.group_id = 1
    ) 
  as admins_in_company
FROM users, companies, users_groups

```