Only return rows if sum is greater than a value

db2, sql, sum

Solution

Whenever you need to do a "WHERE" clause on an aggregate (which `SUM` is), you need to use the `HAVING` clause.

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

Problem

I have built this SQL Query which searches for all policy numbers and returns a sum of all multiple PPOLNO's currency values : ``` 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: ``` 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. Any help would be greatly appreciated. - Josh

Original source