ASA SQL User's Guide
Summarizing, Grouping and Sorting Query Results
Understanding GROUP BY
You can use a WHERE clause in a statement with GROUP BY. The WHERE clause is evaluated before the GROUP BY clause. Rows that do not satisfy the conditions in the WHERE clause are eliminated before any grouping is done. Here is an example:
SELECT name, AVG(unit_price) FROM product WHERE id > 400 GROUP BY name
Only the rows with id values of more than 400 are included in the groups that are used to produce the query results.
The following query illustrates the use of WHERE, GROUP BY, and HAVING clauses in one query:
SELECT name, SUM(quantity) FROM product WHERE name LIKE '%shirt%' GROUP BY name HAVING SUM(quantity) > 100
name | SUM(product.quantity) |
---|---|
Tee Shirt | 157 |
In this example:
The WHERE clause includes only rows that have a name including the word shirt (Tee Shirt, Sweatshirt).
The GROUP BY clause collects the rows with a common name.
The SUM aggregate calculates the total quantity of products available for each group.
The HAVING clause excludes from the final results the groups whose inventory totals do not exceed 100.