Как правильно писать sql?

Question

[Lavrov95]

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 countries ON user.country_id = count' at line 1

Answer 1

[awesomer]

Если вам нужен COUNT со WHERE, то не так делается.
Смотрите HAVING

Comments

[Lavrov95]

user.status выдает тот же результат что и user.id

SELECT countries.short_name, COUNT(user.id), COUNT(user.status) FROM user INNER JOIN countries ON user.country_id = countries.country_id
GROUP BY short_name
HAVING COUNT(user.status = 10);

[awesomer]

Lavrov95, зачем вы условие запихали внутрь скобок у Сount

[Lavrov95]

если user.status != 10 то не рисует в table countries.short_name

SELECT countries.short_name, COUNT(user.id), COUNT(user.status) FROM user INNER JOIN countries ON user.country_id = countries.country_id
GROUP BY short_name
HAVING COUNT(user.status) = 10;

Answer 2

[SagePtr]

Попробуйте так:
SUM(user.status = 1)

Answer 3

[Game Master]

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