Grouping and Aggregation

GROUP BY

Group rows by one or more columns and apply aggregate functions to each group:

SELECT status, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY status

Multiple grouping columns:

SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY customer_id, status

Positional ordinals are also supported. GROUP BY 1, 2 groups by the first and second select-list expressions:

SELECT customer_id, status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY 1, 2

Grouping expressions are also supported directly:

SELECT region || ':' || status AS region_status, SUM(total) AS total_by_status
FROM myschema.orders
GROUP BY region || ':' || status

HAVING

Filter groups after aggregation. You can use both aggregate and non-aggregate predicates:

-- Keep only customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > 5

-- Combine aggregate and non-aggregate conditions
SELECT customer_id, SUM(total) AS total_spent
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 1000 AND customer_id IS NOT NULL

Aggregate Functions

Function	Description
`COUNT(*)`	Count all rows in the group
`COUNT(col)`	Count non-NULL values in `col`
`SUM(col)`	Sum of non-NULL values
`AVG(col)`	Average of non-NULL values
`MIN(col)`	Minimum value
`MAX(col)`	Maximum value
`STDDEV(col)`	Population standard deviation

SELECT
  category,
  COUNT(*) AS total_products,
  COUNT(description) AS with_description,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  STDDEV(price) AS price_stddev
FROM myschema.products
GROUP BY category

DISTINCT Aggregates

Deduplicate values before aggregating:

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM myschema.orders

SELECT SUM(DISTINCT total) AS sum_unique_totals
FROM myschema.orders

Using Aliases in ORDER BY

Aliases defined in the SELECT list are available in ORDER BY. They cannot be used in GROUP BY or HAVING — repeat the expression there instead.

SELECT customer_id, SUM(total) AS lifetime_value
FROM myschema.orders
GROUP BY customer_id
HAVING SUM(total) > 500
ORDER BY lifetime_value   -- alias is valid here

GROUPING SETS

GROUPING SETS lets you compute aggregates for multiple grouping combinations in a single query. Columns not part of a given grouping set are returned as NULL:

SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY GROUPING SETS((region), (status))

ROLLUP

ROLLUP creates subtotals that roll up from the most detailed level to a grand total. ROLLUP(a, b) is equivalent to GROUPING SETS((a, b), (a), ()):

SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY ROLLUP(region, status)

CUBE

CUBE generates subtotals for all possible combinations of the grouping columns. CUBE(a, b) is equivalent to GROUPING SETS((a, b), (a), (b), ()):

SELECT region, status, SUM(total) AS total
FROM myschema.orders
GROUP BY CUBE(region, status)

Not Supported

Window functions (ROW_NUMBER, RANK, SUM OVER, etc.)

Get Started

Guides

MCP Server

API Reference

SQL Reference

Changelog

Grouping and Aggregation

GROUP BY

HAVING

Aggregate Functions

DISTINCT Aggregates

Using Aliases in ORDER BY

GROUPING SETS

ROLLUP

CUBE

Not Supported

​GROUP BY

​HAVING

​Aggregate Functions

​DISTINCT Aggregates

​Using Aliases in ORDER BY

​GROUPING SETS

​ROLLUP

​CUBE

​Not Supported

GROUP BY

HAVING

Aggregate Functions

DISTINCT Aggregates

Using Aliases in ORDER BY

GROUPING SETS

ROLLUP

CUBE

Not Supported