---
title: "PARTITION BY"
description: "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."
canonical: "https://motherduck.com/glossary/partition-by/"
related:
  - title: "MotherDuck Window Functions"
    url: "https://motherduck.com/blog/motherduck-window-functions-in-sql/"
  - title: "Ingest Partitioned S3 Parquet on a Schedule | MotherDuck Docs"
    url: "https://motherduck.com/docs/cookbook/flight-scheduled-s3-ingest/"
  - title: "DuckLake Lakehouse: Getting Started to Going Fast | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-lakehouse-getting-started/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

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

## Overview
`PARTITION BY` is the sub-clause inside a window function's `OVER (...)` that splits the input into independent groups. Unlike `GROUP BY`, which collapses each group into a single output row, `PARTITION BY` keeps every input row intact — the window function is simply computed independently within each partition.

```sql
SELECT
  region,
  salesperson,
  revenue,
  SUM(revenue) OVER (PARTITION BY region) AS region_total,
  revenue / SUM(revenue) OVER (PARTITION BY region) AS pct_of_region
FROM sales;
```

Here every row keeps its own `salesperson` and `revenue`, while `region_total` restarts for each region.

<glossary-callout guide="duckdb-book-brief" />

## PARTITION BY vs GROUP BY
- `GROUP BY region` returns one row per region with aggregated values.
- `PARTITION BY region` (inside `OVER`) returns one row per input row, annotated with a per-region aggregate.

You can combine both: `GROUP BY` to aggregate a base query, then window functions with their own `PARTITION BY` over the aggregated rows.

## DuckDB specifics
Beyond window functions, DuckDB reuses the same `PARTITION BY` keyword for the `COPY ... TO` statement, which writes Hive-partitioned files to disk or object storage — a genuinely different but related concept (physical partitioning rather than logical grouping):

```sql
COPY (SELECT * FROM sales)
TO 's3://my-bucket/sales'
(FORMAT parquet, PARTITION_BY (region, year));
```

This produces a directory structure like `sales/region=EMEA/year=2026/*.parquet`, which query engines (including DuckDB itself via `read_parquet` with hive partitioning enabled) can prune on read.