Query single row from join table and total count of joined table

I have two tables: accounts and users. users references accounts through account_id. What I want to do is get following query to work:

`SELECT accounts.*, count(users.*), user.* 
 FROM accounts
 INNER JOIN users on accounts.id = users.account_id
 INNER JOIN users user on accounts.id = user.account_id limit 1
 GROUP BY accounts.id`

So, I’ll get following results

`| accounts.id  | users_count | user.phone |
 ===========================================
 |     1        |      5      |  xxxxxx    |

Basically, I want to get quantity of all users inside account and specific data for a single user. Is it even possible to do in a single query?

Answer

Assuming you want a single row for each user and account, you can do the following:

SELECT accounts.id,usercount.usercount,users.phone
FROM accounts 
INNER JOIN users 
ON accounts.id = users.account_id
INNER JOIN(
  SELECT count(*) usercount,users.account_id
  FROM users
  GROUP BY account_id
) usercount
on usercount.account_id = accounts.id

If you want data for any single user within the account, you can use get MAX(users.id) to get any user for an account and then fetch data for that user in the result.

SELECT accounts.id,usercount.usercount,users.phone
FROM accounts
INNER JOIN(
  SELECT count(*) usercount,users.account_id,MAX(users.id) as userid
  FROM users
  GROUP BY account_id
) usercount
on usercount.account_id = accounts.id
INNER JOIN users 
ON accounts.id = users.account_id AND usercount.userid = users.id