How to Limit Query Result to Organizations with 2 or More Accounts Associated with it

I have the following code which returns results for sub-organizations (of parent organizations) that do not have an address associated with them. However, this returns 2,000+ rows, many of which have 0 or 1 accounts associated with them.

What I'd like to do is edit the query to only show results where the number of accounts associated with the sub-organization is 2 or more.

In our database, the primary key relating the accounts and organizations tables in a JOIN statement is:

organizations.id = accounts.organization_id

and the primary key on the accounts table is accounts.id

I have tried to figure this out to no avail. If someone could please help provide a solution to limit the results to only sub-organizations with the number of accounts equal to or greater than 2.

SELECT DISTINCT
  organizations.id AS org_id,
  organizations.name AS org_name,
  parent.id AS parent_org_id,
  parent.name AS parent_org_name
FROM
    organizations 
  INNER JOIN organizations parent 
    ON
    organizations.parent_org_id = parent.id
  LEFT JOIN addresses sub_addresses 
    ON
    sub_addresses.addressable_type = 'Organization'
    AND sub_addresses.is_current = 1
    AND sub_addresses.addressable_id = organizations.id
  LEFT JOIN addresses p_addresses 
    ON
    p_addresses.addressable_type = 'Organization'
    AND p_addresses.is_current = 1
    AND p_addresses.addressable_id = parent.id
WHERE
  sub_addresses.id is null
ORDER BY
  parent.name, 
  organizations.name

Solution

You need one more join or subquery in where clause which will give you organizations with at least 2 or more accounts. Below is the join version.

FROM organizations p
INNER JOIN organizations o ON o.parent_org_id = p.id
INNER JOIN (Select a.organization_id,Count(distinct a.id) as num_of_accounts
            FROM accounts a 
            GROUP BY a.organization_id
            HAVING Count(distinct a.id) >= 2) accnts ON accnts.organization_id = o.id