Skip to main content
Aggregate functions compute a single result from a set of input values. They are commonly used with GROUP BY clauses to perform calculations on groups of rows.

​
Standard Aggregate Functions

​
COUNT

Count the number of rows or non-null values.
SignatureReturn TypeDescription
COUNT(*)bigintCount all rows including nulls
COUNT(expr)bigintCount non-null values
COUNT(DISTINCT expr)bigintCount distinct non-null values
Example: 
SELECT COUNT(*) FROM orders;
SELECT COUNT(DISTINCT customer_id) FROM orders;

​
SUM

Calculate the sum of numeric values.
SignatureReturn TypeDescription
SUM(expr)Same as inputSum of all non-null values
SUM(DISTINCT expr)Same as inputSum of distinct non-null values
Example: 
SELECT SUM(amount) FROM transactions;
SELECT customer_id, SUM(order_total) 
FROM orders 
GROUP BY customer_id;

​
AVG

Calculate the average (mean) of numeric values.
SignatureReturn TypeDescription
AVG(expr)numeric or doubleAverage of non-null values
AVG(DISTINCT expr)numeric or doubleAverage of distinct non-null values
Example: 
SELECT AVG(price) FROM products;
SELECT category, AVG(rating) 
FROM products 
GROUP BY category;

​
MIN and MAX

Find the minimum or maximum value.
SignatureReturn TypeDescription
MIN(expr)Same as inputMinimum value
MAX(expr)Same as inputMaximum value
Example: 
SELECT MIN(created_at), MAX(created_at) FROM events;
SELECT product_id, MIN(price), MAX(price) 
FROM price_history 
GROUP BY product_id;

​
STDDEV and VARIANCE

Calculate statistical measures of spread.
FunctionReturn TypeDescription
STDDEV(expr)numericSample standard deviation
STDDEV_POP(expr)numericPopulation standard deviation
STDDEV_SAMP(expr)numericSample standard deviation (same as STDDEV)
VARIANCE(expr)numericSample variance
VAR_POP(expr)numericPopulation variance
VAR_SAMP(expr)numericSample variance (same as VARIANCE)
Example: 
SELECT STDDEV(response_time) FROM requests;
SELECT service, STDDEV(latency), VARIANCE(latency)
FROM metrics
GROUP BY service;

​
Specialized Aggregate Functions

​
array_agg

Aggregate values (including nulls) into an array.
ParameterTypeDescription
valueanyThe values to aggregate
Return Type: array Example: 
SELECT 
    title,
    ARRAY_AGG(
        first_name || ' ' || last_name
        ORDER BY last_name
    ) AS actors
FROM film
GROUP BY title;
Usage Note: Materializing array_agg is an incremental view maintenance anti-pattern. Any change requires complete recomputation. Instead: 
CREATE MATERIALIZED VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW bar AS SELECT array_agg(foo_view.bar) FROM foo_view;

​
string_agg

Concatenate non-null input values into a string with a delimiter.
ParameterTypeDescription
valuetextThe values to concatenate
delimitertextThe separator between values
Return Type: text Example: 
SELECT string_agg(name, ', ' ORDER BY name) AS team_members
FROM employees
WHERE department = 'Engineering';
Usage Note: Like array_agg, materializing string_agg is an anti-pattern: 
CREATE MATERIALIZED VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW bar AS SELECT string_agg(foo_view.bar, ',') FROM foo_view;

​
list_agg

Aggregate values into a list.
ParameterTypeDescription
valueanyThe values to aggregate
Return Type: list Example: 
SELECT list_agg(tag ORDER BY tag) AS tags
FROM article_tags
WHERE article_id = 123;

​
jsonb_agg

Aggregate values (including nulls) as a JSONB array.
ParameterTypeDescription
expressionanyThe values to aggregate
Return Type: jsonb Example: 
SELECT
  jsonb_agg(t ORDER BY t.id) FILTER (WHERE t.content LIKE 'h%') AS my_agg
FROM (
  VALUES
  (1, 'hey'),
  (2, NULL),
  (3, 'hi'),
  (4, 'salutations')
) AS t(id, content);
Result: 
[{"content":"hey","id":1},{"content":"hi","id":3}]

​
jsonb_object_agg

Aggregate key-value pairs into a JSONB object.
ParameterTypeDescription
keysanyThe keys to aggregate
valuesanyThe values to aggregate
Return Type: jsonb Example: 
SELECT
  jsonb_object_agg(
    t.col1,
    t.col2
    ORDER BY t.ts ASC
  ) FILTER (WHERE t.col2 IS NOT NULL) AS my_agg
FROM (
  VALUES
  ('k1', 1, now()),
  ('k2', 2, now() - INTERVAL '1s'),
  ('k2', -1, now())
) AS t(col1, col2, ts);
Result: 
{"k1": 1, "k2": -1}

​
map_agg

Aggregate key-value pairs into a map.
ParameterTypeDescription
keysanyThe keys to aggregate
valuesanyThe values to aggregate
Return Type: map

​
Filtering Aggregates

You can filter the input rows to an aggregate function using the FILTER clause: 
SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

​
Ordering in Aggregates

Some aggregate functions support ORDER BY within the function call: 
SELECT 
    customer_id,
    string_agg(product_name, ', ' ORDER BY order_date DESC) AS recent_products
FROM purchases
GROUP BY customer_id;
Without an explicit ORDER BY clause inside the aggregate function, rows are processed in an arbitrary order.

​
Incremental View Maintenance Considerations

Some aggregate functions work well with incremental updates: Efficient for materialization:
  • COUNT, SUM, MIN, MAX
  • Simple aggregates that can be updated incrementally
Anti-patterns for materialization:
  • array_agg, string_agg, list_agg, jsonb_agg, jsonb_object_agg
  • These require complete recomputation on any change
Best Practice: Materialize the base data, then create non-materialized views with expensive aggregates: 
-- Materialize the filtered, prepared data
CREATE MATERIALIZED VIEW prepared_data AS
SELECT customer_id, product_name, order_date
FROM orders
WHERE status = 'completed';

-- Non-materialized view with expensive aggregate
CREATE VIEW customer_products AS
SELECT 
    customer_id,
    array_agg(product_name ORDER BY order_date) AS products
FROM prepared_data
GROUP BY customer_id;

Build docs developers (and LLMs) love

Get started for freeTalk to us