The previous lesson covered the syntax and mechanics of the GROUP BY clause. This lesson walks through a complete, concrete example from start to finish, using the classic pubs sample database, so you can see exactly what changes in a result set before and after grouping is applied. Reading about GROUP BY in the abstract only gets you so far; watching one query's output transform into another, row by row, is what makes the clause click.
Counting Rows Per Group
Suppose you run a simple SELECT against the pubs database:
SELECT state
FROM authors;
The result is 23 rows of states, ranging from California (15 instances) down to Utah (2 instances); every other state represented appears exactly once. That's useful if you need the raw list, but it's not useful if what you actually want to know is how many authors are in each state.
That's the question GROUP BY answers. Add the COUNT function and a GROUP BY clause, and the same query collapses those 23 rows down to one row per distinct state:
SELECT state, COUNT(state)
FROM authors
GROUP BY state;
Two things are happening in this statement:
The SELECT list names the column (state) and table (authors) you want to pull from, exactly as in the ungrouped version above.
The COUNT function, paired with GROUP BY, condenses every row sharing the same state value into a single summary row, and reports how many original rows fed into each one.
It helps to think of GROUP BY as running in two logical phases, even though the database optimizes the actual execution however it sees fit. First, every row from authors is bucketed into a pile based on its state value; all 15 California rows land in the same pile, all 2 Utah rows land in another. Second, the aggregate function in the SELECT list runs once per pile, producing exactly one output row per pile rather than one output row per input row. That second phase is what makes GROUP BY fundamentally different from a plain WHERE filter, which only ever removes rows, never combines them.
The California group, which started as 15 separate rows, now appears as a single row reading California, 15. Every state that had exactly one author still gets its own row, but now reads 1 in the count column instead of implying it through repetition.
SQL Data Analytics
The GROUP BY clause will condense the rows with the same state, eliminating duplicates.
One practical detail worth flagging: in the query above, the second column has no heading, because it's calculated on the fly and was never given a name. Depending on your client tool, you may see it rendered as blank, NULL, or COUNT(STATE). This is harmless in an ad hoc query you're running yourself, but it becomes a real problem the moment that query feeds a report, a spreadsheet export, or an application that expects a named column to bind to. In practice, you'll almost always want to alias it so the output is self-explanatory both to you and to whatever consumes the result set next:
SELECT state, COUNT(state) AS author_count
FROM authors
GROUP BY state;
COUNT(*) vs. COUNT(column)
Before moving on, it's worth pausing on a distinction that the example above glosses over. COUNT(state) and COUNT(*) don't always return the same number, and the difference matters once your data has gaps in it.
COUNT(*) counts every row in the group, full stop. COUNT(state) counts only the rows in the group where state is not NULL. In the authors example, if every author has a state on file, the two produce identical results:
SELECT state, COUNT(*) AS row_count, COUNT(state) AS non_null_states
FROM authors
GROUP BY state;
But the moment even one row in a group has a NULL in the column you're counting, COUNT(column) will report a smaller number than COUNT(*) for that group. This is a common source of confusion when a report's row count doesn't match its own aggregate columns, so it's worth checking which of the two you actually meant.
The same behavior applies to SUM, AVG, MIN, and MAX: every one of them silently ignores NULL values rather than treating them as zero. This matters most with AVG, since a NULL in the column being averaged doesn't drag the average down toward zero the way you might expect; it's simply excluded from both the total and the count of values being divided. If you actually want NULL treated as zero for a given calculation, you have to say so explicitly, typically with NVL(column, 0) wrapped around the expression before it reaches the aggregate function.
Example of GROUP BY clause
If the column you're grouping on contains blank or NULL values, those rows don't get dropped or raise an error; they simply form their own group. Grouping the authors table by state when a handful of rows have no state on file would produce one extra row in the result set, typically shown as a blank or NULL state, with a count of however many rows were missing that value:
SELECT state, COUNT(*) AS author_count
FROM authors
GROUP BY state
ORDER BY state;
STATE AUTHOR_COUNT
----------- ------------
1
California 15
New York 2
Oregon 1
Utah 2
...
That leading blank row is the NULL group. This is expected behavior, not a bug in the query. It's often the first sign that a report needs a WHERE state IS NOT NULL filter to exclude incomplete records, or that the underlying data has a completeness gap worth investigating with the data owner before the report ships.
Sorting Grouped Results
GROUP BY doesn't guarantee any particular order for its output rows, so if you want the states ranked from most authors to fewest, you still need an explicit ORDER BY, applied to the aggregated column rather than the raw one:
SELECT state, COUNT(state) AS author_count
FROM authors
WHERE state IS NOT NULL
GROUP BY state
ORDER BY author_count DESC;
STATE AUTHOR_COUNT
----------- ------------
California 15
New York 2
Utah 2
Oregon 1
...
Notice that ORDER BY here references the alias (author_count), not the underlying expression, which is one of the few places Oracle lets you refer to a computed column by its alias. Sorting on the aggregate rather than the grouping column turns a plain summary into a ranked one, which is usually what a report's audience actually wants to see first.
Using SQL SUM function Example
Counting rows is only one use of GROUP BY. It's just as common to pair it with SUM, AVG, MIN, or MAX. Here's an example that reports total sales by department:
SELECT department, SUM(sales) AS total_sales
FROM order_details
GROUP BY department;
Because department appears in the SELECT list without being wrapped in an aggregate function, it has to appear in the GROUP BY clause too, exactly the rule covered in the previous lesson. Leaving it out would raise ORA-00979. The result is one row per department, each showing that department's running sales total rather than every individual order line:
Nothing limits you to a single aggregate function per query. A single GROUP BY clause can support as many aggregate functions as you need in the SELECT list, each summarizing the same groups from a different angle:
SELECT department,
COUNT(*) AS order_count,
SUM(sales) AS total_sales,
AVG(sales) AS average_sale,
MAX(sales) AS largest_sale
FROM order_details
GROUP BY department
ORDER BY total_sales DESC;
This single query answers four questions per department at once: how many orders came through, how much revenue they generated in total, what a typical order looked like, and what the single biggest order was. Writing this as four separate queries would mean scanning the order_details table four times; writing it this way scans it once and lets the database compute all four summaries in the same pass. This is exactly the kind of query a dashboard or a monthly report is usually built from.
Grouping by Multiple Columns
Every example so far has grouped by a single column, but GROUP BY accepts a comma-separated list, and each additional column narrows the groups further. Instead of one row per state, you can get one row per state-and-city combination:
SELECT state, city, COUNT(*) AS author_count
FROM authors
GROUP BY state, city
ORDER BY state, city;
STATE CITY AUTHOR_COUNT
----------- --------------- ------------
California Berkeley 3
California Oakland 4
California San Jose 8
New York Ithaca 2
Oregon Portland 1
...
Where the single-column version told you California had 15 authors total, this version breaks that 15 down by city within the state. The rule for the SELECT list stays the same as always: every non-aggregated column, state and city both, must appear in the GROUP BY clause, or Oracle raises the same ORA-00979 error mentioned above. The order of the columns in GROUP BY doesn't change the result set's contents, only the order in which Oracle logically nests the groups, though it's conventional to list them in the same order as the SELECT list for readability.
SQL GROUP BY function
Here's a slightly larger example that ties GROUP BY together with a join, since real reporting queries rarely draw from a single table. Consider these two related tables:
A selection from the "Shippers" table.
The Orders table only stores a ShipperID, not a readable shipper name, so answering "how many orders has each shipper handled?" requires joining the two tables before grouping:
SELECT s.shipper_name, COUNT(o.order_id) AS orders_handled
FROM orders o
JOIN shippers s ON o.shipper_id = s.shipper_id
GROUP BY s.shipper_name;
The join happens first, matching each order to its shipper's name. Only after that match is made does GROUP BY collapse the joined rows down to one line per shipper, with COUNT reporting how many orders each one handled:
SHIPPER_NAME ORDERS_HANDLED
----------------- --------------
Speedy Express 412
United Package 389
Federal Shipping 297
This is a common shape for real reporting queries: join to bring in the human-readable label, then group and aggregate to produce the summary. You can extend it further by adding a WHERE clause before the GROUP BY, exactly as with the earlier authors example, to scope the report to a specific window of time:
SELECT s.shipper_name, COUNT(o.order_id) AS orders_handled
FROM orders o
JOIN shippers s ON o.shipper_id = s.shipper_id
WHERE o.order_date >= DATE '2025-07-01'
AND o.order_date < DATE '2025-08-01'
GROUP BY s.shipper_name
ORDER BY orders_handled DESC;
Nothing about the grouping logic changes. The WHERE clause simply narrows the set of order rows that reach the join and, eventually, the GROUP BY, so the resulting counts reflect a single month rather than the table's entire history. This is the same filter-then-group-then-aggregate order covered in the previous lesson, just applied across a joined result set instead of a single table.
Before You Move On
Before continuing to the next lesson, it's worth predicting the output of one more variation on your own. Take the department sales query from earlier and imagine adding WHERE sales > 500 before the GROUP BY. Which rows get excluded, the individual order lines under $500, or entire departments whose total falls under $500? If your answer is "individual order lines, because WHERE runs before grouping," you've internalized the core idea this lesson was built around. If you're not sure, it's worth re-reading the SUM example above with that question in mind before moving on to HAVING in the next lesson, since HAVING is precisely the tool for that second, department-total version of the question.
Common Mistakes to Watch For
Two errors account for nearly every GROUP BY problem you'll run into while building queries like the ones above.
The first is the one already mentioned: including a plain column in the SELECT list without also including it in the GROUP BY clause, or wrapping it in an aggregate function. Oracle responds with ORA-00979: not a GROUP BY expression, and the fix is always the same, either add the column to GROUP BY or aggregate it.
The second is trying to filter on an aggregate value using WHERE instead of HAVING. A query like WHERE COUNT(order_id) > 100 will fail, because WHERE filters individual rows before grouping happens, and no individual row has a COUNT value to compare against. Filtering grouped results by an aggregate condition is the HAVING clause's job, which the next lesson covers directly. As a rule of thumb: if the condition you're writing needs to look at an already-summarized value, whether that's a total, a count, or an average, it belongs after the grouping has happened, not before it.
Looking Ahead
You've now seen GROUP BY used several different ways across this lesson:
Counting rows per group, and the difference between COUNT(*) and COUNT(column) when nulls are present
Sorting grouped output by an aggregated column rather than the grouping column itself
Handling NULL and blank values, which form their own group rather than causing an error
Summing a numeric column, and combining several aggregate functions in a single pass
Grouping by more than one column to get a finer-grained breakdown
Aggregating across a join, with a WHERE clause narrowing the rows before grouping ever happens
Together these examples cover the great majority of GROUP BY queries you'll write in practice. The next lesson introduces the HAVING clause, which lets you filter these grouped results after aggregation, for example, showing only shippers who've handled more than a certain number of orders, something WHERE alone can't do.
