Subqueries and Aggregates

Introduction

Subqueries nest one query inside another—filter with IN, test with EXISTS, or build inline tables. Aggregate functions summarize rows; GROUP BY and HAVING build reports. This chapter adds PostgreSQL extras: FILTER (WHERE ...) on aggregates and optional ROLLUP subtotals. Compare with MySQL subqueries and MySQL GROUP BY.

Prerequisites

• Joins and CTEs
• Sample data in users and posts

Scalar Subquery

Returns a single value—one row, one column:

SELECT
  title,
  (SELECT display_name FROM users WHERE id = posts.user_id) AS author
FROM posts;

Post count per user in the SELECT list:

SELECT
  display_name,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;

Code explanation:

• Correlated subqueries run per outer row—fine on small data; watch performance at scale

Subquery With IN

Users who published at least one post:

SELECT display_name
FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM posts WHERE published_at IS NOT NULL
);

Equivalent join:

SELECT DISTINCT u.display_name
FROM users u
INNER JOIN posts p ON p.user_id = u.id
WHERE p.published_at IS NOT NULL;

EXISTS

True if the subquery returns any row:

SELECT display_name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p
  WHERE p.user_id = u.id AND p.published_at IS NULL
);

Finds users with draft posts.

Code explanation:

• SELECT 1 inside EXISTS—the value does not matter
• Often stops at first match—optimizer-friendly with indexes

IN vs EXISTS

Rule of thumb: EXISTS for "has related row" checks; IN for small static sets.

Table Subquery (Derived Table)

Subquery in FROM needs an alias:

SELECT author, post_count
FROM (
  SELECT u.display_name AS author, COUNT(*) AS post_count
  FROM users u
  INNER JOIN posts p ON p.user_id = u.id
  GROUP BY u.id, u.display_name
) AS stats
WHERE post_count >= 2;

Prefer a CTE when the same subquery is reused—Joins and CTEs.

Aggregate Functions

Whole table:

SELECT COUNT(*) AS total_posts FROM posts;
 
SELECT
  COUNT(*) AS all_posts,
  COUNT(published_at) AS published_posts,
  COUNT(*) FILTER (WHERE published_at IS NULL) AS drafts
FROM posts;

Code explanation:

• COUNT(*) counts rows
• COUNT(col) ignores NULL in col
• FILTER (WHERE ...) is PostgreSQL syntax—MySQL often uses SUM(CASE WHEN ...)

Other aggregates:

SELECT
  MIN(published_at) AS first_publish,
  MAX(published_at) AS last_publish
FROM posts
WHERE published_at IS NOT NULL;

AVG, SUM apply to numeric types—use NUMERIC for money.

GROUP BY

Posts per user:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id;

With author name via join:

SELECT
  u.display_name,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.display_name;

Code explanation:

• Every non-aggregated SELECT column must appear in GROUP BY
• PostgreSQL enforces this strictly—unlike older MySQL ONLY_FULL_GROUP_BY surprises

COUNT DISTINCT

Unique authors who published:

SELECT COUNT(DISTINCT user_id) AS publishing_users
FROM posts
WHERE published_at IS NOT NULL;

Conditional counts with FILTER:

SELECT
  user_id,
  COUNT(*) FILTER (WHERE published_at IS NOT NULL) AS published,
  COUNT(*) FILTER (WHERE published_at IS NULL) AS drafts
FROM posts
GROUP BY user_id;

HAVING vs WHERE

WHERE filters rows before grouping:

SELECT user_id, COUNT(*) AS published_count
FROM posts
WHERE published_at IS NOT NULL
GROUP BY user_id;

HAVING filters groups after aggregation:

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) >= 2;

Authors with at least two published posts:

SELECT u.display_name, COUNT(p.id) AS published_count
FROM users u
INNER JOIN posts p ON p.user_id = u.id
WHERE p.published_at IS NOT NULL
GROUP BY u.id, u.display_name
HAVING COUNT(p.id) >= 2;

ORDER BY With Aggregates

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC, user_id;

Top authors by published count:

SELECT u.display_name, COUNT(p.id) AS published_count
FROM users u
INNER JOIN posts p ON p.user_id = u.id
WHERE p.published_at IS NOT NULL
GROUP BY u.id, u.display_name
ORDER BY published_count DESC
LIMIT 5;

ROLLUP Subtotals (Optional)

ROLLUP adds subtotal and grand total rows:

SELECT
  user_id,
  DATE_TRUNC('month', published_at) AS month,
  COUNT(*) AS post_count
FROM posts
WHERE published_at IS NOT NULL
GROUP BY ROLLUP (user_id, DATE_TRUNC('month', published_at))
ORDER BY user_id NULLS LAST, month NULLS LAST;

Code explanation:

• NULL in grouping columns often marks subtotal rows—interpret carefully in apps
• CUBE and GROUPING SETS add more rollup shapes—see official docs for reporting

Subquery in HAVING

Groups where total published exceeds average:

SELECT user_id, COUNT(*) AS published_count
FROM posts
WHERE published_at IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) > (
  SELECT AVG(cnt) FROM (
    SELECT COUNT(*) AS cnt
    FROM posts
    WHERE published_at IS NOT NULL
    GROUP BY user_id
  ) AS averages
);

A CTE often reads cleaner than nested subqueries here.

FAQ

Subquery vs JOIN?

Prefer joins when both sides are large and indexed—often clearer and equally fast. Use EXISTS for existence checks.

FILTER vs CASE?

COUNT(*) FILTER (WHERE condition) is concise PostgreSQL. SUM(CASE WHEN condition THEN 1 END) works everywhere.

Can HAVING use column aliases?

In PostgreSQL, HAVING post_count >= 2 works when post_count is a SELECT list alias in simple queries—prefer HAVING COUNT(*) >= 2 in portable SQL.

GROUP BY primary key only?

If all selected non-aggregate columns come from one table and you GROUP BY that table's primary key, other columns from the same row are functionally determined—PostgreSQL allows this.

Why is my aggregate slow?

Missing indexes on JOIN / WHERE columns—check EXPLAIN ANALYZE in chapter 10.

Array or JSONB aggregates?

array_agg, jsonb_agg, string_agg—covered in JSONB and Advanced Types.