How to write sql correctly?

Question

SQL

SELECT countries.short_name, COUNT(user.id), COUNT(user.id WHERE user.status = 1) FROM user INNER JOIN countries ON user.country_id = countries.country_id
GROUP BY short_name

ERROR
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE user.status = 1) FROM user INNER JOIN ON user.country_id = count' at line 1

Answer 1

If you need a COUNT with a WHERE, that's not how it's done.
See HAVING

Answer 2

Try this:
SUM(user.status = 1)

Answer 3

SELECT 
countries.short_name, 
COUNT(user.id) as cnt_all_stat_user, 
COALESCE(sum(case when user.status = 1 then 1 end),0) as cnt_stat_1_user
FROM user 
INNER JOIN countries 
  ON user.country_id = countries.country_id
GROUP BY short_name