Aggregate Functions

Introduction

Aggregate functions collapse many rows into one summary value: COUNT, SUM, AVG, MIN, MAX. They power every dashboard, KPI and report.

Understanding the topic

Core concepts to understand:

• COUNT(*) counts rows; COUNT(col) counts non-null values.
• SUM, AVG ignore NULLs.
• Use with GROUP BY to aggregate per group.
• Filter aggregates with HAVING.
• Postgres adds STRING_AGG, ARRAY_AGG, BOOL_OR.

Syntax reference

Visual workflow / architecture:

SELECT country,
       count(*)        AS users,
       sum(spend)      AS revenue,
       avg(spend)      AS avg_spend
FROM   users
GROUP  BY country
HAVING count(*) > 100
ORDER  BY revenue DESC;

Real-world use

Every analytics dashboard, KPI tile, and BI chart is built on aggregates. Stripe Dashboard, Mixpanel, Amplitude — all SUM/COUNT/AVG over big tables.

Best practices

• Always know whether NULLs should be counted.
• Index group columns for fast GROUP BY.
• Pre-aggregate into reporting tables for hot dashboards.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. COUNT(*) vs COUNT(col).
• Q2. Does AVG include NULLs?
• Q3. Difference between WHERE and HAVING.
• Q4. STRING_AGG use case.
• Q5. How to handle nulls in SUM?