Lesson 8 of 20

In progress

Course progress

SQL

GROUP BY

Split rows into groups and summarise each one, with the rule that decides which columns may appear in the SELECT list.

GROUP BY is where SQL starts to feel like a reporting language. It partitions rows by a value, then applies aggregates to each partition instead of the whole table.

What you will learn

  • Group by one or several columns.
  • Follow the rule for non-aggregated columns.
  • Group by expressions and read the result in a stable order.

Grouping by one column

SQL
Editable. Runs in your browser.





Press Run to see the output.

One row per project, with counts and totals computed inside each group. The grouping column is the only plain column you are allowed to select.

That rule is worth stating plainly: every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate. SQLite is lenient and will return an arbitrary value for the others; PostgreSQL rejects the query, and SQLite’s answer is not meaningful.

Grouping by several columns

SQL
Editable. Runs in your browser.





Press Run to see the output.

Groups are the distinct combinations, so site/high and site/low are separate. The number of rows in the result is at most the product of the distinct values, and usually much less.

Filtering before grouping

SQL
Editable. Runs in your browser.





Press Run to see the output.

WHERE removes rows before the groups exist. Only closed tickets are counted, and a project with no closed tickets disappears entirely rather than appearing with zero. That is a real reporting decision: use WHERE when the population changes, and keep the rows when the absence itself matters.

Grouping by an expression

SQL
Editable. Runs in your browser.





Press Run to see the output.

The alias works in GROUP BY in SQLite and MySQL; PostgreSQL requires repeating the expression. Repeating it is the portable choice, and a CASE in two places is a good sign the logic deserves a view.

A very common reporting group is a date bucket: GROUP BY SUBSTR(closed_on, 1, 7) for a month, or GROUP BY DATE(created_at) in databases with real date types.

Order of groups

GROUP BY produces groups in no guaranteed order. Add ORDER BY whenever a human will read the result:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Sorting by the aggregate is allowed, and the second key keeps ties stable.

NULL as a group

NULL forms its own group. If priority were nullable, the result would include a row with a null priority, and COUNT(*) would count those rows. That is often correct, and it occasionally looks like a bug in a report where a blank label appears at the top.

Group nulls explicitly when the report needs a label:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Grouping and performance

Grouping requires the engine to bring equal keys together, either by sorting or by hashing. An index on the grouped column can supply the order for free, which is why GROUP BY customer_id with an index on that column is usually fast and the same query without the index is not.

The number of groups matters as much as the number of rows: ten million rows in five groups is cheap to hash, while ten million rows in ten million groups is not. When a grouped query is slow, check the plan for a temporary B-tree, then decide whether an index or a narrower filter is the better fix. Measure before adding an index, because every index also slows inserts.

A realistic summary

SQL
Editable. Runs in your browser.





Press Run to see the output.

The CASE inside SUM counts a subset without a second query. That trick, called conditional aggregation, is the workhorse of dashboards and is worth remembering as a pattern rather than a formula.

Selecting a column that is neither grouped nor aggregated. SQLite returns an arbitrary row’s value, which looks plausible and is not defined. Group it, aggregate it, or remove it.

Report, per priority, the number of tickets, total hours, and how many are still open. Order by total hours descending.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

High-priority tickets account for 31.5 hours across four tickets, one group per priority. The CASE counts open tickets while COUNT(*) counts all of them, which is why both columns are needed.

Recap

  • GROUP BY makes one row per distinct combination; other columns must be aggregated.
  • WHERE filters rows before grouping; groups appear in no guaranteed order.
  • Conditional aggregation with SUM(CASE ...) counts subsets in the same pass.

Next, HAVING filters the groups themselves, which WHERE cannot do.

Can I GROUP BY a column number?
Some databases accept `GROUP BY 1` meaning the first select column. It breaks when the select list changes, so name the column.
Why is my grouped query slow?
Often a missing index on the grouping column, or a large sort. Check the query plan before adding indexes.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.