how to properly use where clause in sql?-CodePudding

SELECT supplierid, SUM(price) 
FROM Products 
GROUP BY supplierid  
ORDER BY SUM(price) DESC 
WHERE SUM(price) > 200;

WHERE clause keeps me stuck

CodePudding user response：

for aggregation function applied to distinct values you need a proper group by clause and use HAVING (not where) for filter aggregated result

SELECT supplierid, SUM(price) 
FROM Products 
GROUP BY supplierid  
HAVING SUM(price) > 200
ORDER BY SUM(price) DESC

CodePudding user response：

A WHERE clause is used to define scalar filtering predicates on a query and can only be placed following the FROM - and any JOINs - and before any grouping, aggregating, or ordering clauses.

As the WHERE may only contain scalar predicates, it can not contain any aggregate functions such as (non-exhaustive) COUNT, SUM, AVG, MIN, or MAX.

The example provided in the question is invalid syntax, and contains no valid predicates for use in a WHERE clause.