SQL 仅当 sum 大于某个值时才返回行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16320362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Only return rows if sum is greater than a value
提问by ResourceReaper
I have built this SQL Query which searches for all policy numbers and returns a sum of all multiple PPOLNO's currency values :
我构建了这个 SQL 查询，它搜索所有保单号码并返回所有多个 PPOLNO 的货币值的总和：
SELECT PPOLNO, SUM(PPRMPD) AS SUM
FROM PFNTLPYMTH
WHERE SUM(PPRMPD) >= 5000
AND ((PYEAR=2012 AND PMONTH >=3
AND PDAY >=27) OR (PYEAR=2013
AND PYEAR <=3 AND PDAY<=27))
GROUP BY PPOLNO
What I'm looking at doing is only returning them if the SUM >= a specific value. Is this possible and how? I tried this:
我正在做的只是在 SUM >= 特定值时才返回它们。这是可能的吗？我试过这个：
SELECT PPOLNO, SUM(PPRMPD) AS SUM FROM PFNTLPYMTH
WHERE SUM(PPRMPD) >= 5000 AND ((PYEAR=2012 AND PMONTH >=3 AND PDAY >=27) OR (PYEAR=2013
AND PYEAR <=3 AND PDAY<=27)) GROUP BY PPOLNO
But to no avail. I've also just tried putting in WHERE SUM >= 5000, but again, nothing there.
但无济于事。我也试过输入 WHERE SUM >= 5000，但同样没有。
Any help would be greatly appreciated.
任何帮助将不胜感激。
- Josh
- 乔希
回答by Derek Kromm
Whenever you need to do a "WHERE" clause on an aggregate (which SUMis), you need to use the HAVINGclause.
每当您需要对聚合（即SUM）执行“WHERE”子句时，您都需要使用该HAVING子句。
SELECT PPOLNO, SUM(PPRMPD) AS SUM FROM PFNTLPYMTH
WHERE ((PYEAR=2012 AND PMONTH >=3 AND PDAY >=27) OR (PYEAR=2013
AND PYEAR <=3 AND PDAY<=27)) GROUP BY PPOLNO
HAVING SUM(PPRMPD) >= 5000
回答by Charles Bretana
Predicates in the Whereclause are applied beforeaggregation, To apply a predicate (filter) afteraggregation, use a Havingclause... try
中谓语Where从句应用前聚集，要应用谓词（过滤器）后，聚集，使用Having条款等试
SELECT PPOLNO, SUM(PPRMPD) AS SUM
FROM PFNTLPYMTH
WHERE ((PYEAR=2012 AND PMONTH >=3
AND PDAY >=27) OR (PYEAR=2013
AND PYEAR <=3 AND PDAY<=27))
GROUP BY PPOLNO
Having SUM(PPRMPD) >= 5000
