how to properly use where clause in sql?

Question

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

WHERE clause keeps me stuck

`WHERE` comes before `ORDER BY` and `GROUP BY`. [Here's the relevant documentation](https://dev.mysql.com/doc/refman/8.0/en/select.html); note that the order is `SELECT`, `FROM`, `WHERE`, `GROUP BY`, `ORDER BY`. — D M, Jan 14 '22 at 16:36
Where appears before group by - see https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all you are probably looking for group by...having — P.Salmon, Jan 14 '22 at 16:38

score 1 · Answer 1 · answered Jan 14 '22 at 16:40

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

score 0 · Answer 2 · answered Jan 14 '22 at 16:44

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.

how to properly use where clause in sql?

2 Answers2