These three operations are typically performed using the following clauses in a SELECT statement:
| Operation | SQL Clause | Function |
| Filtering | WHERE | Selects which rows to include in the result set. |
| Aggregation | GROUP BY & HAVING | Groups rows based on common values and applies summary functions. |
| Sorting | ORDER BY | Specifies the order in which the final result set is returned. |
Filtering Data with WHERE
The WHERE clause is used to filter individual rows based on specified conditions. It is processed before any aggregation.
Syntax & Example
SELECT column1, column2
FROM table_name
WHERE condition;Example: Find all products with a price greater than $50.00.
SELECT product_name, price
FROM products
WHERE price > 50.00;Common Operators: =, !=, >, <, >=, <=, LIKE (pattern matching), IN (list of values), BETWEEN (range), IS NULL.
Aggregation with GROUP BY and HAVING
Aggregation involves calculating summary values (like sums, averages, or counts) for groups of rows.
1. GROUP BY
The GROUP BY clause groups rows that have the same values in specified columns. Aggregate functions then operate on these groups.
Example: Count the number of products in each category.
SELECT category_id, COUNT(product_id) AS product_count
FROM products
GROUP BY category_id;2. HAVING (Filtering Aggregated Results)
The HAVING clause is used to filter the results of a GROUP BY operation, similar to how WHERE filters individual rows. It is processed after aggregation.
Example: Find only the categories that have more than 10 products.
SELECT category_id, COUNT(product_id) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(product_id) > 10;Sorting Data with ORDER BY
The ORDER BY clause sorts the final result set based on the values in one or more columns. It is the last clause executed in the logical flow.
Syntax & Example
SELECT column1, column2
FROM table_name
ORDER BY sort_column1 [ASC|DESC], sort_column2 [ASC|DESC];ASC(Ascending – default): Sorts from lowest to highest (A-Z, 1-10).
DESC(Descending): Sorts from highest to lowest (Z-A, 10-1).
Example: List employee salaries, sorted from highest to lowest.
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;Combined Example
Here’s how these clauses work together in a single query:
-- 1. Get the average salary (Aggregation)
-- 2. For each department (Grouping)
-- 3. Filter only for departments with an average salary over 60000 (Having/Filter on Group)
-- 4. Order the results by the average salary, lowest first (Sorting)
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE department IS NOT NULL -- WHERE filters individual rows before aggregation
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary ASC;