Advanced Uses of the SELECT Statement: Grouping and Filtering
Once you're comfortable with basic SELECT statements, the next step is learning how to ask more sophisticated questions of your data. Simple queries retrieve rows as-is, but real-world reporting usually requires summarizing data, narrowing it down to what matters, and eliminating redundant results. This lesson introduces three tools that make this possible:
Grouping — combining rows into summary groups with GROUP BY
Filtering — restricting which rows or groups appear, with WHERE and HAVING
Eliminating duplicates — using DISTINCT to keep results meaningful
Understanding what your database engine is capable of — and matching that to your reporting requirements — is the foundation of writing effective advanced queries.
Grouping Data with GROUP BY
The GROUP BY clause collects rows that share the same value in one or more columns into a single group, so that aggregate functions can be applied to each group as a whole rather than to individual rows.
For example, given a sales table with columns product_id, price, and sale_date, you can calculate total revenue per product like this:
SELECT product_id, SUM(price) AS total_revenue
FROM sales
GROUP BY product_id;
This groups every row by product_id and uses SUM() to add up the price values within each group, returning one row of output per product.
Aggregate (Group) Functions
Aggregate functions operate on a group of rows and return a single summary value for that group. The most common ones are:
COUNT(*) -- number of rows in the group
MIN(exp) -- minimum value of the expression across the group
MAX(exp) -- maximum value of the expression across the group
AVG(exp) -- average value of the expression across the group
SUM(exp) -- sum of the expression across the group
For example, to count how many "Pepsi" items are in a fridge table:
SELECT COUNT(*)
FROM fridge
WHERE pop = 'Pepsi';
If no WHERE clause is specified, COUNT(*) simply returns the total number of rows in the table.
Eliminating Duplicates with DISTINCT
When a query returns repeated values that add no new information, DISTINCT collapses them down to unique rows. For example, to list each unique city that has a sales office, without repeats:
SELECT DISTINCT city
FROM offices;
This keeps result sets concise and easier to interpret, especially when reporting on data with many repeated values.
Filtering Data with WHERE and HAVING
The WHERE clause filters individual rows before any grouping or aggregation takes place. It can be used with SELECT, UPDATE, and DELETE statements to restrict which rows are affected.
For example, to retrieve only sales that occurred in May 2023:
SELECT product_id, price, sale_date
FROM sales
WHERE sale_date >= '2023-05-01' AND sale_date <= '2023-05-31';
The HAVING clause, by contrast, filters the results of a GROUP BY operation after grouping and aggregation have occurred. It's used specifically to test conditions on aggregate values, something WHERE cannot do.
For example, to show only products whose total revenue exceeds $10,000:
SELECT product_id, SUM(price) AS total_revenue
FROM sales
GROUP BY product_id
HAVING SUM(price) > 10000;
This groups the data by product_id, computes total revenue for each group, and then filters out any product that doesn't clear the $10,000 threshold.
Putting It Together
As a reminder, the exact formatting of query results varies from one SQL product to another, but the underlying logic is consistent across platforms. Consider a request like "list the sales offices with their targets and actual sales":
SELECT city, target, sales
FROM offices;
CITY TARGET SALES
------------- ------------- -------------
Denver $300,000.00 $186,042.00
New York $575,000.00 $692,637.00
Chicago $800,000.00 $735,042.00
Atlanta $350,000.00 $367,911.00
Los Angeles $725,000.00 $835,915.00
City, Target, and Sales by Office
For simple requests, the English-language question and the SQL SELECT statement look almost identical. As requests grow more complex — summarizing totals, filtering on conditions, or excluding duplicates — you'll rely on GROUP BY, WHERE, HAVING, and DISTINCT together to express exactly what you need. The rest of this module builds directly on these four tools.