SQL:聚合后如何过滤? [英] SQL: How to filter after aggregation?

问题描述

删除不想聚合的值非常容易.

It is very easy to remove values that you don't want aggregated.

例如:

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

这将从求和聚合中排除所有值小于或等于 1000 的销售额.

Which will exclude all sales with a value less than or equal to 1000 from the summing aggregation.

但是聚合后如何过滤?

例如WHERE(总销售额"> 15000)

具有讽刺意味的是，我只包括 HAVING SUM(sales) >1000; 以防止混淆所需的查询类型；因为我实际上对从求和中排除项目不感兴趣，只对返回的结果感兴趣！谢谢，尽管很困惑！

Ironically I was only including HAVING SUM(sales) > 1000; in order to prevent confusion about the type of query required; because I'm not actually interested in excluding items from the summing, just the returned results! Thanks, despite confusion!

推荐答案

您的查询实际上是在做您想做的事情，而不是您在问题中表达的内容.如果要排除所有值小于 1000 的销售额，则应使用 WHERE sales >1000.但是 HAVING SUM(sales) >1000过滤实际上是在聚合之后完成的.

The query you have is actually doing what you want and not what you expressed in the question. If you want to exclude all sales with a value less than 1000, you should use WHERE sales > 1000. But with HAVING SUM(sales) > 1000 the filtering is actually done after the aggregation.

编写子查询并在原始查询之上添加另一个 SELECT WHERE 是多余的.

Writing a sub-query and adding another SELECT WHERE on top of the original query is redundant.

请参阅 fiddle 以获得说明.

Please see fiddle for clarification.

#Query1

SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING Total > 40;

#Query 2

SELECT department, SUM(sales) as Total
FROM order_details
GROUP BY department
HAVING  SUM(sales) > 40;

#Query 3

SELECT department, SUM(sales) as Total
FROM order_details
WHERE sales > 40
GROUP BY department;

#Query 1 and 2 are the same, filtering after aggregation
#Query 3 is filtering before aggregation

这篇关于SQL:聚合后如何过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！