使用 where 子句聚合 sql [英] aggregate sql with a where clause

问题描述

我遇到了这个查询的问题.

SELECT adm_Consultant, count(adm_Consultant) as num从入学WHERE adm.adm_ReferralDate >= '01/01/2014 00:00:00' AND adm.adm_ReferralDate <= '31/12/2014 00:00:00'AND adm.adm_PriorSurgery = '是'AND adm.adm_Consultant <>''GROUP BY adm_Consultant

<块引用>

错误:一般错误

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

SELECT adm_Consultant, count(adm_Consultant) as num从入学GROUP BY adm_Consultant

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

请帮忙.

这是我的阅读材料.

COUNT(expr)

<块引用>

返回由 SELECT 语句检索的行中 expr 的非 NULL 值的计数.结果是一个 BIGINT 值.

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

解决方案

您忘记创建别名 adm

SELECT adm_Consultant, count(adm_Consultant) as numFROM 招生官WHERE adm.adm_ReferralDate >= '01/01/2014 00:00:00' ANDadm.adm_ReferralDate <= '31/12/2014 00:00:00'AND adm.adm_PriorSurgery = '是'AND adm.adm_Consultant <>''GROUP BY adm_Consultant

Im having trouble with this query.

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

this works though, but returns the null values as-well

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

I tried the HAVING clause instead of the WHERE clause, but still it fails.

Please help.

here was my reading material.

COUNT(expr)

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

解决方案

You are forgetting to create alias 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

这篇关于使用 where 子句聚合 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！