Aggregate Functions and GROUP BY

Aggregate functions compute a single result from a set of rows. Standard aggregates include COUNT, SUM, AVG, MIN, and MAX. PostgreSQL also provides STRING_AGG for concatenating strings, ARRAY_AGG for building arrays, BOOL_AND and BOOL_OR for boolean aggregation, and JSON_AGG for building JSON arrays. GROUP BY divides rows into groups before aggregation, and HAVING filters groups after aggregation (equivalent to WHERE for aggregated results). The FILTER clause on an aggregate applies a per-aggregate WHERE condition, allowing multiple conditional aggregates in a single query. GROUPING SETS, ROLLUP, and CUBE generate multiple groupings in one pass — useful for subtotals and cross-tabulation reports.

Example

-- Basic aggregates
SELECT
  category_id,
  COUNT(*)                             AS total_products,
  COUNT(*) FILTER (WHERE is_active)    AS active_products,
  SUM(stock)                           AS total_stock,
  ROUND(AVG(price)::NUMERIC, 2)        AS avg_price,
  MIN(price)                           AS min_price,
  MAX(price)                           AS max_price,
  STRING_AGG(name, ', ' ORDER BY name) AS product_names
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2
ORDER BY total_products DESC;

-- ROLLUP — subtotals and grand total
SELECT
  category_id,
  status,
  SUM(total)  AS revenue,
  COUNT(*)    AS orders
FROM orders
GROUP BY ROLLUP (category_id, status);
-- Generates: (category, status), (category), ()

-- ARRAY_AGG — collect values into an array
SELECT user_id, ARRAY_AGG(product_id ORDER BY added_at) AS wishlist
FROM wishlists
GROUP BY user_id;

-- JSON_AGG — aggregate rows into a JSON array
SELECT
  u.id,
  u.name,
  JSON_AGG(JSON_BUILD_OBJECT('id', o.id, 'total', o.total)) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned