SQL GROUPS

What is SQL GROUPS?

Specifies a window frame measured in peer groups (ties in ORDER BY) instead of individual rows or logical ranges.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GROUPS: Supported: PostgreSQL 14+, Oracle 12c+, Google BigQuery. Not supported: MySQL 8.0, SQLite 3.x, SQL Server 2019.

SQL GROUPS Full Explanation

GROUPS is one of the three frame units (ROWS, RANGE, GROUPS) available in window functions. When you declare a frame with GROUPS, the database groups adjacent rows with equal ORDER BY values into "peer groups" and then applies the boundary logic to those groups rather than to single rows. This is useful when you want calculations like running totals, moving averages, or ranking statistics to respect ties in the ordering column.Key points:- Requires an ORDER BY clause inside the window definition.- Frame boundaries may use UNBOUNDED PRECEDING/FOLLOWING, CURRENT ROW, or a signed integer followed by PRECEDING/FOLLOWING.- The integer boundaries count peer groups, not physical rows.- If two rows share the same ORDER BY values they belong to the same peer group and are always either wholly inside or outside the frame.- Supported in the SQL:2012 standard and later, but not all engines implement it yet.

SQL GROUPS Syntax

<window function> OVER (
  [PARTITION BY expr_list]
  ORDER BY sort_expr [ASC|DESC]
  GROUPS BETWEEN <start_boundary> AND <end_boundary>
);

SQL GROUPS Parameters

start_boundary (keyword (UNBOUNDED, CURRENT) or integer) - Defines the lower edge of the frame.
end_boundary (keyword (UNBOUNDED, CURRENT) or integer) - Defines the upper edge of the frame.

Example Queries Using SQL GROUPS

-- Running total that includes the current and previous peer group
SELECT order_id,
       order_date,
       SUM(amount) OVER (
         ORDER BY order_date
         GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

-- Count rows from the two preceding groups through the next group
SELECT event_time,
       COUNT(*) OVER (
         ORDER BY event_time
         GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING
       ) AS grp_window_cnt
FROM events;

Expected Output Using SQL GROUPS

Each row receives a calculated value that includes all rows in the specified peer-group window
Tied ORDER BY values are included or excluded as a whole group, never partially

Use Cases with SQL GROUPS

Compute running totals that treat identical timestamps as a single step.
Calculate moving averages that should not split ties.
Rank statistics where all rows in a tie share the same aggregated window.

Common Mistakes with SQL GROUPS

Using GROUPS without an ORDER BY clause.
Assuming the integer boundaries count rows rather than peer groups.
Confusing GROUPS with GROUP BY; GROUPS is only for window frames.
Expecting support in engines like MySQL 8.0 or SQLite, which do not implement GROUPS.

First Introduced In

SQL:2012

Frequently Asked Questions

What does GROUPS do in a window function?

GROUPS tells the database to measure the window frame in peer groups defined by equal ORDER BY values instead of individual rows.

Is GROUPS the same as GROUP BY?

No. GROUP BY aggregates the entire result set once, whereas GROUPS only affects how a window frame is sliced for each row.

Which databases support GROUPS?

At the time of writing: PostgreSQL 14+, Oracle 12c+, and Google BigQuery. Engines like MySQL, SQLite, and most SQL Server versions do not yet support it.

Can I mix GROUPS with ROWS or RANGE?

A single window definition can only use one frame unit. Choose either ROWS, RANGE, or GROUPS for that window.