---
title: "GROUP BY clause"
description: "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."
canonical: "https://motherduck.com/glossary/group-by-clause/"
related:
  - title: "Query syntax | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/query-syntax/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

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

```sql
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:

```sql
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:

```sql
-- 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.

## Related Concepts
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](https://motherduck.com/learn/sql-group-by-examples-duckdb/)