When using an aggregate function, you need to add a HAVING statement to achieve the same filtering effect as a WHERE statement.
Syntax
SELECT [ ALL | DISTINCT ]{ * | projectItem [, projectItem ]* }
FROM tableExpression
[ WHERE booleanExpression ]
[ GROUP BY { groupItem [, groupItem ]* } ]
[ HAVING booleanExpression ];
Example
- Test data
Customer OrderPrice Bush 1000 Carter 1600 Bush 700 Bush 300 Adams 2000 Carter 100 - Test statement
SELECT Customer,SUM(OrderPrice) FROM XXX GROUP BY Customer HAVING SUM(OrderPrice)<2000;
- Test result
Customer SUM(OrderPrice) Carter 1700