SQL for Analytics
Intermediate 14 min read SQL for Analytics

SQL Aggregations

From event rows to business summaries

GROUP BY turns rows into summaries: counts, sums, averages, min/max and distinct counts, plus window-style totals. Getting aggregation right is the core of analytical SQL.

GROUP BYaggregatesCOUNTSUMDISTINCTHAVINGAVG
Prerequisites: SQL for Analysts
Before we start

What you will learn

  • Aggregate with COUNT, SUM, AVG, MIN, MAX
  • Understand distinct counts and their pitfalls
  • Group by multiple columns and filter groups
Introduction

What is this?

Aggregations collapse many rows into summary statistics. GROUP BY partitions rows by the grouped columns, then each aggregate function runs within its bucket — COUNT(*) counts rows, COUNT(DISTINCT x) counts unique x, SUM/AVG aggregate numerics. HAVING filters the resulting groups. Correct grouping is the difference between 'revenue by country' and a misleading mash.

Motivation

Why do you need it?

  • Business questions are group-and-summarize questions: by cohort, segment, region, day.
  • Aggregates reveal structure hidden in raw event floods.
  • A wrong GROUP BY is the #1 source of silently wrong dashboards.
  • Understanding aggregation makes you fluent in pivots, cohort tables and funnels.
Think about it

Mental model

Imagine

A census bureau

Think of

GROUP BY

The bureau doesn't hand you every citizen; it counts by city, age and income bucket. You specify the buckets (GROUP BY) and the summary (COUNT/SUM) — same raw census, infinite summaries.

Mechanics

How it works

  • GROUP BY country buckets rows by distinct country values.
  • Aggregate functions run inside each bucket: SUM(amount) per country.
  • COUNT(*) counts rows; COUNT(DISTINCT user_id) counts unique users.
  • HAVING filters buckets after aggregation (revenue > 10k).
  • Multi-column GROUP BY groups by the combination, e.g., country + month.

Rows → buckets → summaries.

Rows
GROUP BY
Aggregates
HAVING
Result
Hands-on

Examples

Aggregation essentials

Aggregation essentials
SELECT
  date_trunc('month', date) AS month,
  COUNT(DISTINCT user_id)   AS active_users,
  COUNT(*)                  AS events,
  SUM(amount)               AS revenue,
  AVG(amount)               AS aov
FROM orders
WHERE date >= '2026-01-01'
GROUP BY 1
HAVING SUM(amount) > 10000
ORDER BY month;

Buckets by month, then computes distinct users, event count, revenue and average order value per month, keeping only months above a revenue floor.

Real world

In production

  • Daily active users computed with COUNT(DISTINCT user_id) grouped by day.
  • Monthly revenue roll-ups feeding the board dashboard.
  • Average order value monitoring flagged when aov drops 5% versus the prior month.
Guidelines

Best practices

  • Group by the exact grain you want; extra columns in GROUP BY change the answer.
  • Use COUNT(DISTINCT ...) sparingly — it is expensive on huge tables.
  • Give aggregates clear aliases (revenue, active_users) so dashboards read well.
  • Add HAVING only when you must filter groups; filter rows with WHERE first.
Watch out

Common mistakes

  • SELECT columns that aren't grouped or aggregated — invalid in most engines, and wrong where allowed.
  • COUNT(user_id) instead of COUNT(DISTINCT user_id) when users repeat.
  • WHERE before aggregation when you wanted to filter groups (use HAVING).
  • Comparing group counts over different date windows without aligning grain.
When things break

Debugging tips

  • Distrust an anomaly — recount with both COUNT(*) and COUNT(DISTINCT key).
  • Align windows: group by the same calendar definition before comparing days.
  • Check for NULL in group columns — they become a mystery group.
  • Cross-check one bucket manually with a filtered subquery.
Interviews

Interview checkpoint

Practice

Hands-on lab

Aggregate Event Data into Metrics
25 min · intermediate
In progress
Objectives
  • Group events by day and user type
  • Compute totals and distinct counts
  • Use HAVING to filter buckets

Aggregate Event Data into Metrics

25 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which clause filters aggregated groups after GROUP BY?

2

COUNT(DISTINCT col) counts the number of rows where col is non-null.

Go deeper

Official documentation

PostgreSQL aggregate functions
docs