mysql - 使用 where 子句聚合 sql

Question

我在处理这个查询时遇到了问题。

SELECT adm_Consultant, count(adm_Consultant) as num
FROM Admission
WHERE  adm.adm_ReferralDate >= '01/01/2014 00:00:00' AND adm.adm_ReferralDate <= '31/12/2014 00:00:00'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY adm_Consultant

ERROR: General error

这虽然有效，但也返回空值

SELECT adm_Consultant, count(adm_Consultant) as num
FROM Admission
GROUP BY adm_Consultant

我尝试了 HAVING 子句而不是 WHERE 子句，但仍然失败。

请帮忙。

这是我的阅读 Material 。

COUNT(表达式)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

Answer 1

您忘记创建别名 adm

SELECT adm_Consultant, count(adm_Consultant) as num 
FROM Admission adm
WHERE  adm.adm_ReferralDate >= '01/01/2014 00:00:00' AND 
adm.adm_ReferralDate <= '31/12/2014 00:00:00'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY adm_Consultant