The HAVING clause filters groups produced by GROUP BY based on a condition. In MaxCompute SQL, WHERE cannot filter on aggregate function results because WHERE runs before aggregation. Use HAVING instead — it is evaluated after groups and aggregates are computed.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator valueExamples
The following examples use an Orders table with these columns: Customer, OrderPrice, Order_date, and Order_id.
Filter by aggregate function result
Return customers whose total order amount is less than 2,000:
SELECT Customer, SUM(OrderPrice)
FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice) < 2000| Customer | SUM(OrderPrice) |
|---|---|
| Smith | 1800 |
| Brown | 950 |
Filter by a GROUP BY column
Return the order total for a specific customer:
SELECT Customer, SUM(OrderPrice)
FROM Orders
GROUP BY Customer
HAVING Customer = 'Smith'| Customer | SUM(OrderPrice) |
|---|---|
| Smith | 1800 |
该文章对您有帮助吗？