How to COUNT “accounts” only if character/user exist

I have 2 tables – “accounts” and “players”

I’m using this query to COUNT accounts

SELECT COUNT(*) FROM `accounts` WHERE `id` > 1;

Accounts table estructure:

id, name

Players table estructure:

id, name, account_id

*

Let’s say the query counts 10 accounts but only 3 accounts have a character/user created.

I would like to know how to count only those 3 accounts without taking in consideration how many characters/users has the account, so we count it without repeating.

Answer

You could use exists to filter out accounts that have no linked players:

SELECT COUNT(*) no_accounts
FROM accounts a 
WHERE 
    a.id > 1
    AND EXISTS (
        SELECT 1 FROM players p WHERE p.account_id = a.id
    )