SQL GROUP BY Clause

The SQL GROUP BY clause is used in a SELECT statement to collapse multiple rows that share a common value into a single summary row. Rather than returning every individual sales transaction, every login event, or every order line, GROUP BY lets the database do the work of consolidating that raw detail into totals, counts, and averages you can actually use.

Understanding when to reach for GROUP BY, not just how to write its syntax, is the real skill this lesson builds. If a request can be phrased as "for each X, show me some summary of Y," that phrasing is your signal that GROUP BY belongs in the query. "For each department, what's the average salary?" "For each customer, how many orders have they placed?" "For each product category, what's total revenue this quarter?" Each of these maps directly onto a GROUP BY clause paired with an aggregate function.

Contrast that with a request like "show me a list of every distinct job title in the company." No summarization is happening there, just duplicate elimination, so DISTINCT is the right tool, not GROUP BY. Recognizing which category a request falls into, before you write a single line of SQL, will save you from reaching for the wrong clause.

The basic form of the GROUP BY clause is:

SELECT expression1, expression2, ... expression_n,
       aggregate_function(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

expression1, expression2, ... expression_n	Non-aggregated expressions in the SELECT list. Each one must also appear in the GROUP BY clause.
aggregate_function	An aggregate function such as SUM, COUNT, MIN, MAX, or AVG.
aggregate_expression	The column or expression the aggregate_function operates on.
tables	The table(s) to retrieve records from. There must be at least one table listed in the FROM clause.
conditions	Row-level conditions that must be met before grouping occurs.

Oracle evaluates the expressions in GROUP BY against any column of the tables, views, or materialized views named in the FROM clause; the columns don't need to appear in the SELECT list at all. Keep in mind that GROUP BY groups rows but does not guarantee the order of the result set. If you need a specific order, add an ORDER BY clause.

Most reporting requests boil down to the same shape: retrieve detail rows, then reduce them to something a person can act on. A raw list of ten thousand order rows doesn't tell a sales manager much on its own; a list of total revenue per product does.

GROUP BY does this reduction inside the database engine, before the result set ever reaches your application. That matters for two reasons. First, it's almost always faster than pulling every row across the network and summing it in application code. Second, it keeps the summarization logic in one place, the query, rather than scattered across whatever client happens to be consuming the data.

The trade-off to understand up front: once you group rows together, the individual row-level detail is gone from that result set. If you need both the summary and the detail, you'll either run two queries or use an analytic (window) function instead, which is covered later in this module.

It's worth being explicit about a distinction that trips up a lot of newer SQL writers: GROUP BY and DISTINCT can sometimes produce the same rows, but they solve different problems.

DISTINCT simply removes duplicate rows from a result set; no aggregation happens. GROUP BY exists specifically to pair with aggregate functions like COUNT, SUM, AVG, MIN, and MAX. If your query has no aggregate function in it, DISTINCT is almost always the clearer choice:

SELECT DISTINCT department_id
FROM employees;

But the moment you need a count, a total, or an average per group, GROUP BY is the only option:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

A useful rule of thumb: if you find yourself writing SELECT DISTINCT and then wishing you could also show a count next to each distinct value, that's the signal to switch to GROUP BY.

Five aggregate functions cover the vast majority of GROUP BY use cases:

COUNT, SUM, MAX, MIN, AVG

COUNT returns the number of rows (or non-null values) matching the query. SUM, MAX, MIN, and AVG operate on numeric values and return the total, largest value, smallest value, and mean, respectively. MAX and MIN can also be applied to non-numeric columns, as long as the column's data type supports a total ordering; dates and character strings both qualify.

A simple aggregate query with no grouping at all returns one row summarizing the entire table:

SELECT SUM(salary), MAX(salary), MIN(salary), AVG(salary)
FROM employees;

Add a GROUP BY clause, and the same functions instead produce one summary row per group. Using Oracle's sample employees table, to find the minimum and maximum salary in each department:

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;

Run the department query above and you'll get one row per department, each showing that department's salary floor and ceiling:

DEPARTMENT_ID   MIN(SALARY)   MAX(SALARY)
-------------   -----------   -----------
           10          4400          4400
           20          6000         13000
           30          2500         11000
           50          2100         11000
           60          4200          9000
           ...

That's the entire point of GROUP BY in one result set: forty-plus rows of raw employee data reduced down to one meaningful line per department.

Add a WHERE clause to narrow the rows considered before grouping, for example, to see the same salary range but only among clerks:

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
WHERE job_id = 'PU_CLERK'
GROUP BY department_id
ORDER BY department_id;

Notice that WHERE filters rows before grouping happens, while grouping and aggregation occur afterward. This ordering, filter first, then group, then aggregate, is fundamental to how the database processes the statement, and it's the reason WHERE can't reference an aggregate function like SUM() or COUNT(). Filtering on an aggregate result is what the HAVING clause is for, which the next lesson covers in depth.

GROUP BY isn't limited to grouping on raw column values; you can group on the result of an expression. This query counts how many employees were hired in each calendar year:

SELECT TRUNC(hire_date, 'YYYY') year_hired, COUNT(*)
FROM employees
GROUP BY TRUNC(hire_date, 'YYYY')
ORDER BY year_hired;

Here's a subtlety worth internalizing: if a column alias in your SELECT list happens to match an actual column name from the source table, Oracle resolves that identifier in the GROUP BY clause to the column, not the alias. This rarely matters, but it can produce a confusing result if you're not expecting it, so name your aliases distinctly from your source columns when practical.

If you take one practical warning away from this lesson, make it this one: every non-aggregated column in your SELECT list must also appear in the GROUP BY clause. Leave one out, and Oracle raises ORA-00979: not a GROUP BY expression.

-- This fails with ORA-00979
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id;

The query above asks for job_id in the output, but job_id isn't part of the grouping and isn't wrapped in an aggregate function, so Oracle has no way to know which job_id value to display for a department that has, say, five different job titles within it. The fix is either to add job_id to the GROUP BY clause, or to wrap it in an aggregate function like MIN(job_id) if you genuinely only need a representative value:

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;

This single rule, every plain column in the SELECT list must be grouped, accounts for the majority of GROUP BY errors new SQL developers run into, and it's worth committing to memory before moving on.

A common follow-on request is to aggregate a count that's already been grouped, for example, finding the total number of orders placed, but derived from a per-customer breakdown rather than a flat COUNT(*) on the whole table. This is typically done with a subquery: the inner query groups and counts, and the outer query sums those counts.

SELECT SUM(order_count) AS total_orders
FROM (
    SELECT COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) counts;

The inner query produces one row per customer_id, each holding that customer's order count. The outer query then treats those per-customer counts as its own data set and sums them into a single total. In this particular example the result is mathematically identical to a plain SELECT COUNT(*) FROM orders, but the same pattern becomes genuinely useful once the inner grouping applies its own filtering, for instance, summing only the counts from customers who placed more than five orders, using a HAVING clause inside the subquery.

A related, and often simpler, need is counting conditionally within a single group rather than counting across separate groups. Oracle AI Database 26ai adds a FILTER clause to aggregate functions specifically for this: it applies a condition directly to the values an aggregate function considers, without a subquery.

SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'SHIPPED') AS shipped_orders,
       COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled_orders
FROM orders
GROUP BY customer_id;

This produces, in a single pass, two separately conditioned counts per customer, something that previously required either a CASE expression inside SUM() or multiple subqueries joined together. It's a small addition, but it removes a lot of boilerplate from analytic queries that need several conditional totals side by side.

Oracle AI Database 26ai adds three enhancements aimed squarely at making GROUP BY faster to write and less error-prone.

GROUP BY ALL

Complex SELECT lists with several aggregate functions traditionally require every non-aggregated column to be repeated, verbatim, in the GROUP BY clause. The new GROUP BY ALL clause removes that repetition: it automatically groups by every non-aggregated expression in the SELECT list.

SELECT department_id, job_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY ALL;

GROUP BY ALL is a reserved-syntax option, meaning you can't combine it with ROLLUP, CUBE, or GROUPING SETS; if you specify ALL, it must be the entire group_by_clause. It also has a few carve-outs worth knowing: it excludes group functions, scalar subqueries, and window functions from consideration, and it skips constant expressions, including NULLs and bind variables, to avoid ambiguity when positional grouping is also enabled. GROUP BY ALL is supported in views, materialized views, and WITH clause queries, and a HAVING condition can still be added alongside it, though it isn't supported with the MODEL clause.

For quick prototyping or ad hoc analysis, this is a genuine time-saver, especially while you're still iterating on which columns belong in the SELECT list and don't want to keep two lists (the SELECT list and the GROUP BY list) in sync by hand. For production code that other developers will maintain, spelling out the grouping columns explicitly is often still the more readable choice, since GROUP BY ALL requires a reader to mentally re-derive which columns are being grouped by scanning the entire SELECT list for anything that isn't wrapped in an aggregate function.

GROUP BY ALL also works cleanly in a WITH clause query, which makes it a convenient fit for the kind of exploratory, multi-step queries you'll build later in this course:

WITH dept_summary AS (
    SELECT department_id, job_id, COUNT(*), AVG(salary)
    FROM employees
    GROUP BY ALL
)
SELECT * FROM dept_summary
ORDER BY department_id;

GROUP BY Column Alias or Position

26ai also allows GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS to reference a SELECT-list column alias or its ordinal position, rather than repeating the full expression:

SELECT TRUNC(hire_date, 'YYYY') AS year_hired, COUNT(*)
FROM employees
GROUP BY year_hired
ORDER BY year_hired;

The HAVING clause gains the same ability to reference column aliases. One important caveat: grouping by ordinal position only works once the group_by_position_enabled parameter is set to true at the session or system level. It's disabled by default, so don't assume positional grouping will work in an environment you haven't configured yourself.

A Preview of ROLLUP, CUBE, and GROUPING SETS

The group_by_clause syntax also supports three extensions, ROLLUP, CUBE, and GROUPING SETS, that generate multiple levels of subtotal alongside the detail groups, useful for cross-tabulated reports and running totals. These are powerful enough to deserve their own dedicated treatment, which is exactly what the next few lessons in this module provide.

A handful of restrictions apply to every GROUP BY clause, regardless of which Oracle version you're running:

• No LOB columns, nested tables, or varrays. These data types don't have a well-defined equality comparison the way a NUMBER or VARCHAR2 does, so the database has no reliable way to decide which rows belong in the same group.
• Object type columns disable parallelization. If your GROUP BY clause references a column defined on a user-created object type, Oracle will run that query serially rather than splitting it across parallel server processes; worth knowing if you're troubleshooting why an otherwise-parallelizable report is running slower than expected.
• Positional grouping is opt-in. Grouping by ordinal position (GROUP BY 1, 2) only works once group_by_position_enabled is explicitly set to true, at either the session or system level. It's false by default, so don't assume it will work in an environment you haven't configured yourself, and don't rely on it in code that has to run in environments you don't control.

Keeping these limits in mind up front will save you from chasing a confusing error message, or an unexpectedly slow execution plan, later on.

GROUP BY answers the question "how do I summarize rows that share a value?" The next lesson tackles the natural follow-up: how do you filter those summarized groups themselves, once they've already been collapsed down to totals and averages? That's the job of the HAVING clause, and it builds directly on everything covered here.

Between this lesson and the next, you now have the two building blocks that every summary report is built from: a way to collapse detail rows into groups, and, coming up, a way to decide which of those groups are worth showing. Once both are second nature, reading and writing real-world reporting queries becomes a matter of recognizing the pattern rather than reconstructing the syntax from scratch each time. Keep the ORA-00979 rule, the "WHERE before grouping, HAVING after grouping" ordering, and the GROUP BY vs. DISTINCT distinction close at hand; those three ideas alone resolve most of the confusion beginners run into with this clause.