Three clauses shape almost every reporting query you will ever write: ORDER BY sorts the rows, GROUP BY folds many rows into one per group, and HAVING filters those groups after the totals are computed. Learn how they fit together and dashboards, summaries and leaderboards stop being guesswork.
What this guide covers
1. ORDER BY: sorting the result
By default a query returns rows in no guaranteed order. The engine is free to hand them back in whatever sequence is cheapest, and that sequence can change between runs. If you want a predictable order you must ask for it with ORDER BY. It is the last thing the database does before it sends rows to you, which is why it can sort by columns and aliases the rest of the query has already produced.
The simplest form sorts by one column. Add ASC for ascending, the default, or DESC for descending.
-- newest orders first
SELECT id, customer, placed_at
FROM orders
ORDER BY placed_at DESC;Sorting by more than one column
List several columns and the database sorts by the first, then breaks ties with the second, and so on. Each column carries its own direction, so you can mix ascending and descending in the same clause. This is exactly how a spreadsheet sorts when you pick a primary and secondary key.
-- by country A to Z, then highest spenders first inside each country
SELECT country, customer, total_spent
FROM customers
ORDER BY country ASC, total_spent DESC;| country | customer | total_spent |
|---|---|---|
| Canada | Rao | 4100 |
| Canada | Blume | 900 |
| Kenya | Otieno | 2750 |
| Kenya | Wangari | 2750 |
Notice the two Kenyan rows tie on total_spent. When rows tie on every sort key their relative order is undefined, so if the tie matters, add another column such as customer to make the sort deterministic.
2. NULLS FIRST, NULLS LAST and sorting by expression
NULL values sort too, and where they land depends on the engine. PostgreSQL and Oracle place NULLs last in ascending order, while MySQL and SQL Server place them first. The portable way to be explicit is the NULLS FIRST or NULLS LAST modifier, supported by PostgreSQL, Oracle and SQLite.
-- push rows with an unknown price to the bottom
SELECT name, price
FROM products
ORDER BY price ASC NULLS LAST;On MySQL, which lacks the modifier, you emulate it by sorting on an IS NULL test first: ORDER BY price IS NULL, price. The boolean sorts unknown rows to the end before the real prices are ordered. For a deeper look at how the unknown behaves everywhere in SQL, see the guide to NULL values.
Sort by an expression, an alias or a position
You are not limited to bare columns. ORDER BY can sort by a computed expression, by the alias you gave a column in the SELECT list, or by the ordinal position of a column. All three of the queries below sort by the same computed total.
-- by expression
SELECT name, qty * unit_price AS line_total
FROM order_lines
ORDER BY qty * unit_price DESC;
-- by the alias, cleaner and computed only once
SELECT name, qty * unit_price AS line_total
FROM order_lines
ORDER BY line_total DESC;
-- by position: 2 means the second SELECT column
SELECT name, qty * unit_price AS line_total
FROM order_lines
ORDER BY 2 DESC;Sorting by alias works because ORDER BY runs after the SELECT list has been evaluated, so the alias already exists. Ordering by position is compact but fragile: if you reorder the SELECT columns later the number silently points at a different column. Prefer the alias in code you plan to keep. The official PostgreSQL SELECT lists documentation spells out how output column names and positions can be used in ORDER BY.
Remember: without ORDER BY there is no order at all. Never rely on rows arriving sorted just because they happened to last time. If the sequence matters, state it.
3. GROUP BY: how grouping actually works
GROUP BY collapses rows that share the same values into a single summary row per group. Picture the engine sorting the rows into buckets: every row with country Kenya goes in one bucket, every row with country Canada in another. After the buckets are formed, the query produces exactly one output row for each bucket. This is the key idea: one row per group, never one row per original record.
-- one output row for each distinct country
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country;| country | customers |
|---|---|
| Canada | 2 |
| Kenya | 2 |
| Japan | 1 |
You can group by more than one column. GROUP BY country, status makes one group for each distinct pair of values, so a country with three statuses can produce up to three rows. All NULLs in a grouping column collapse into a single group, which is the one exception to the rule that NULL never equals NULL.
The rule that trips everyone up
Here is the constraint you must internalise. Once a query has a GROUP BY, every column in the SELECT list must either appear in the GROUP BY or be wrapped in an aggregate function. The reason is simple: a group is one row, so a plain column that varies within the group has no single value to show.
-- which customer name? the group has several. this is ambiguous
SELECT country, customer, COUNT(*)
FROM customers
GROUP BY country;-- every non-aggregated column is in the GROUP BY
SELECT country, status, COUNT(*) AS n
FROM customers
GROUP BY country, status;Standard databases such as PostgreSQL and SQL Server reject the wrong query with a clear error. Older MySQL configurations would quietly pick an arbitrary value for the ungrouped column, which is a well known source of silent bugs. Modern MySQL enables ONLY_FULL_GROUP_BY by default to enforce the standard rule.
4. Aggregate functions with GROUP BY
Aggregates are the whole point of grouping. An aggregate function takes all the rows in a group and reduces them to one value. The five you will use constantly are below. With one exception they all skip NULL inputs, which matters most for AVG because it divides by the count of non-NULL values.
| Function | Returns | Counts NULL? |
|---|---|---|
COUNT(*) | Number of rows in the group | Yes, counts every row |
COUNT(col) | Number of non-NULL values | No, skips NULL |
SUM(col) | Total of the values | No, skips NULL |
AVG(col) | Mean of non-NULL values | No, skips NULL |
MIN(col) / MAX(col) | Smallest / largest value | No, skips NULL |
A single grouped query can combine many aggregates at once. This is how you build a compact summary table from raw transaction data.
SELECT country,
COUNT(*) AS orders,
SUM(amount) AS revenue,
AVG(amount) AS avg_order,
MAX(amount) AS biggest
FROM orders
GROUP BY country;For the full behaviour of each function, including COUNT(DISTINCT ...) and string aggregates, see the dedicated guide to SQL aggregate functions and the wider SQL functions list. You can also run an aggregate with no GROUP BY at all, in which case the whole table is treated as one group and you get a single summary row.
5. HAVING vs WHERE
This is the distinction that separates confident report writers from the rest. WHERE filters individual rows before they are grouped. HAVING filters whole groups after aggregation. Because WHERE runs before the groups exist, it cannot see aggregate results, so a condition like SUM(amount) > 1000 is illegal in WHERE but perfectly valid in HAVING.
| Question | WHERE | HAVING |
|---|---|---|
| When does it run? | Before grouping | After grouping |
| What does it filter? | Individual rows | Whole groups |
| Can it see aggregates? | No | Yes |
| Can it use plain columns? | Yes | Only grouped columns |
| Typical use | Remove unwanted raw rows | Keep groups that pass a total test |
The example below reads naturally once you see the split. WHERE throws away cancelled orders before counting, then the query groups by country and sums, and finally HAVING keeps only the countries whose surviving revenue tops 5000.
SELECT country, SUM(amount) AS revenue
FROM orders
WHERE status <> 'cancelled' -- filter rows first
GROUP BY country
HAVING SUM(amount) > 5000 -- then filter groups
ORDER BY revenue DESC;Watch out: putting a plain row condition in HAVING when it belongs in WHERE still returns the right answer, but it is slower, because the database groups rows it could have discarded earlier. Filter rows in WHERE, filter totals in HAVING. The WHERE clause guide covers row filtering in full.
6. Combining WHERE, GROUP BY, HAVING and ORDER BY
A complete analytical query stacks all four clauses, and they always appear in the same written order: WHERE, then GROUP BY, then HAVING, then ORDER BY. Writing them out of order is a syntax error. The query below finds active product categories that sold more than 100 units in 2025, sorted by the strongest sellers.
SELECT category,
SUM(qty) AS units_sold,
COUNT(DISTINCT order_id) AS orders
FROM sales
WHERE sold_at >= '2025-01-01'
AND sold_at < '2026-01-01'
GROUP BY category
HAVING SUM(qty) > 100
ORDER BY units_sold DESC;category units_sold orders
----------- ----------- ------
Beverages 842 190
Snacks 530 121
Dairy 214 58Any category with 100 units or fewer never reaches the result, because HAVING removed its group. Rows dated outside 2025 never counted, because WHERE removed them first. This layering is what makes SQL reporting so expressive: each clause has one clear job.
7. Logical processing order
You write a query starting with SELECT, but the database does not evaluate it in that order. Understanding the true sequence explains almost every apparent oddity, such as why you can sort by an alias but cannot filter by one in WHERE. The logical order is shown below.
FROM and JOIN
Assemble the working set of rows from the source tables.
WHERE
Filter individual rows. Aliases from SELECT do not exist yet.
GROUP BY
Fold the surviving rows into one row per group.
HAVING
Filter whole groups using aggregate results.
SELECT
Evaluate expressions and assign column aliases.
ORDER BY
Sort the finished rows. Aliases now exist, so they can be used.
Two practical consequences fall straight out of this list. First, WHERE cannot reference a SELECT alias because the alias is not defined until step five, whereas ORDER BY in step six can. Second, HAVING can reference aggregates because it runs after grouping, while WHERE cannot. Keep this ladder in mind and the rules stop feeling arbitrary. The DQL commands reference and the SQL cheat sheet both restate this order for quick recall.
HAVING shows up least because most filtering belongs in WHERE. You only reach for HAVING when the condition depends on an aggregate, which is a narrower need than sorting or row filtering.
8. GROUPING SETS, ROLLUP and CUBE
Sometimes one grouping is not enough. You want per country totals and a grand total in the same result, or subtotals at several levels. Writing separate queries and stitching them with UNION ALL works but is verbose. The GROUPING SETS, ROLLUP and CUBE extensions do it in one pass.
ROLLUP adds progressively coarser subtotals along a hierarchy. Grouping by ROLLUP(country, city) produces a row per city, a subtotal per country, and one grand total row where both columns are NULL.
SELECT country, city, SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP(country, city);| country | city | revenue |
|---|---|---|
| Kenya | Nairobi | 3200 |
| Kenya | Mombasa | 1500 |
| Kenya | NULL | 4700 |
| NULL | NULL | 4700 |
The rows where a column is NULL are the subtotal and grand total lines. CUBE goes further and produces every combination of the grouping columns, useful for cross tab reports. GROUPING SETS gives you full manual control, letting you list exactly which groupings you want. Syntax differs a little across engines, so check the MySQL GROUP BY modifiers documentation for the WITH ROLLUP spelling MySQL uses.
These features shine in summary dashboards where you need totals at several levels at once. When you outgrow them, the next step is usually window functions, which compute running totals and ranks without collapsing rows. The window functions guide picks up exactly where grouping leaves off.
Putting it together
These clauses form a pipeline. WHERE trims the raw rows, GROUP BY folds them into one row per group, aggregate functions summarise each group, HAVING keeps the groups that matter, and ORDER BY presents the result in a sensible order. Write them in that order, remember that WHERE filters rows while HAVING filters groups, and keep the logical processing sequence in your head so aliases and aggregates land where they are allowed. Do that and grouped reporting becomes routine. To practise every one of these clauses on runnable data, work through the SQL beginner course.