Объединить запросы к разным таблицам MySQL

Для MySQL сработает такой запрос

SELECT
  COUNT(*),
  u.`id`,
  u.`username`,
  u.`avatar`,
  u.`name`,
  u.`description`
FROM
  `users` u
  LEFT JOIN `followers` f ON (u.`username` = f.`user`)
WHERE
  u.`username` = :username
GROUP BY
  u.`username`

Попробуйте через подзапрос:

SELECT id, 
    username, 
    avatar, 
    name, 
    description,
    ifnull((SELECT COUNT(*) FROM followers WHERE user = :username), 0) as followers_count,
    ifnull((SELECT COUNT(*) FROM followers WHERE follower = :username), 0) as followings_count
FROM users 
WHERE username = :username