使用WHERE子句计数记录的查询 [英] Query that Counts records with a WHERE clause

问题描述

                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

having子句限制聚合，而where子句只限制单个列数据

The having clause restricts a aggregate whereas the where clause only put restrictions on individual column data

这篇关于使用WHERE子句计数记录的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！