SELECT
EmailOfConsumer,
COUNT(EmailOfConsumer) as 'NumberOfOrders',
SUM(CAST(Total as money)) as 'TotalValue',
(SUM(CAST(Total as money))/COUNT(EmailOfConsumer)) as 'AverageValue'
FROM webshop
GROUP BY EmailOfConsumer
ORDER BY TotalValue DESC
这带来了：
EmailOfConsumer NumberOfOrders TotalValue AverageValue
test 1 2000000000.10 2000000000.10
我想添加一个搜索 WHERE NumberOfOrders = '1'
我尝试添加 WHERE COUNT(EmailOfConsumer) = '1'
但是我得到这个错误：
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a
HAVING clause or a select list, and the column being aggregated is an outer reference.
使用
HAVING COUNT(EmailOfConsumer) = 1
hading子句限制聚合，而where子句仅对单个列数据施加限制
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句