Writing robust data pipelines with AILivestream July 22

START FREE
← Back to Glossary

GROUP BY clause

GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.

Overview

The GROUP BY clause is a fundamental SQL operation that allows you to organize rows into groups based on one or more columns, enabling aggregate calculations on each group rather than the entire dataset. When you use GROUP BY, each group will contain all rows that share the same values in the specified grouping columns.

Basic Usage

The most common use of GROUP BY involves aggregating data by a single column. For example, to count orders by country:

Copy code
SELECT country, COUNT(*) as order_count
FROM orders 
GROUP BY country;

Multiple Column Groups

You can group by multiple columns to create more specific groupings. The order of columns in the GROUP BY doesn't affect the results:

Copy code
SELECT country, product_category, SUM(revenue) as total_revenue
FROM sales
GROUP BY country, product_category;

DuckDB-Specific Features

DuckDB extends the standard GROUP BY functionality with helpful features like GROUP BY ALL, which automatically groups by all non-aggregated columns in the SELECT clause:

Copy code
-- These queries are equivalent in DuckDB
SELECT country, region, SUM(sales) 
FROM orders
GROUP BY country, region;

SELECT country, region, SUM(sales) 
FROM orders
GROUP BY ALL;

Common Pitfalls

When using GROUP BY, all columns in the SELECT clause must either be included in the GROUP BY clause or be wrapped in an aggregate function (like SUM, COUNT, AVG). DuckDB will return an error if this rule is violated, helping prevent accidental misuse of grouping operations.

The GROUP BY clause is often used with:

  • HAVING clause to filter grouped results
  • Window functions for more complex aggregations
  • ORDER BY to sort the grouped results
  • Aggregate functions like COUNT, SUM, AVG, MAX, and MIN

For more advanced grouping operations in DuckDB, look into GROUPING SETS, ROLLUP, and CUBE clauses which provide additional ways to aggregate data at multiple levels simultaneously.

Learn more: 5 Examples of SQL GROUP BY in Action

Related terms

HAVING clause →

The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().

GROUPING SETS →

GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.

row group →

A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…

aggregate functions →

Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.

PARTITION BY →

PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.

FILTER clause →

The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.

FAQS

It organizes rows into groups that share the same values in the specified columns, so aggregate functions (COUNT, SUM, AVG…) are computed per group rather than across the whole table.

Yes. List several columns to create more specific groupings; the order of the grouping columns doesn't change the results.

A DuckDB shortcut that automatically groups by every non-aggregated column in the SELECT list, so you don't have to repeat them in the GROUP BY clause.

READ NEXT