COUNT(), AVG(), SUM()

Learning Focus

Use this lesson to understand COUNT(), AVG(), SUM() with practical syntax and examples.

Concept Overview

Aggregate functions summarize many rows into a single value (or one value per group). Three of the most common aggregates are:

• COUNT() - how many rows/values
• SUM() - total of a numeric expression
• AVG() - average of a numeric expression

Why is it important?

• Business reporting: revenue, order volume, active users, conversion rates
• Data validation: detect missing values, unexpected totals, and changes over time
• Decision support: turn raw events into dashboards and KPIs

Where does it fit?

These aggregates are typically used in SELECT statements, often combined with WHERE (filter rows), GROUP BY (per-group metrics), and HAVING (filter groups). PostgreSQL also supports the FILTER clause for conditional aggregates.

---

Syntax & Rules

Core Syntax

SELECT
  COUNT(*) AS row_count,
  COUNT(col) AS non_null_count,
  SUM(numeric_expr) AS total,
  AVG(numeric_expr) AS average
FROM table_name
WHERE conditions;

Available Options / Parameters

Function	Common form	Notes
COUNT	COUNT(*)	counts rows (includes NULLs because it counts rows)
COUNT	COUNT(col)	counts non-NULL values in col
COUNT	COUNT(DISTINCT col)	counts distinct non-NULL values
SUM	SUM(col)	ignores NULL values
AVG	AVG(col)	ignores NULL values
Conditional	SUM(x) FILTER (WHERE ...)	PostgreSQL feature; avoids CASE boilerplate

Key Rules and Considerations

• SUM() and AVG() ignore NULLs by default.
• COUNT(*) counts rows; COUNT(col) counts non-NULL values.
• After joins, counts and sums can be inflated if the join multiplies rows. This is the #1 source of incorrect metrics.
• Always alias aggregates in reporting queries.

---

Step-by-Step Examples

Example 1: COUNT(*) vs COUNT(col) (Beginner)

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      numeric(12,2)
);

INSERT INTO employees (full_name, department, salary) VALUES
  ('Alice', 'Sales', 50000.00),
  ('Bob', 'Sales', 60000.00),
  ('Charlie', 'HR', 40000.00),
  ('David', 'HR', 42000.00),
  ('Eva', 'IT', NULL);

SELECT
  COUNT(*) AS total_employees,
  COUNT(salary) AS employees_with_salary
FROM employees;

Expected output:

 total_employees | employees_with_salary
-----------------+----------------------
 5               | 4
(1 row)

Explanation:

• COUNT(*) counts all 5 rows.
• COUNT(salary) ignores the NULL salary.

---

Example 2: AVG and SUM Ignore NULLs (Intermediate)

SELECT
  SUM(salary) AS sum_salary,
  AVG(salary) AS avg_salary
FROM employees;

Expected output:

 sum_salary | avg_salary
------------+--------------------
 192000.00  | 48000.000000000000
(1 row)

Explanation:

• The NULL salary does not contribute to the sum or average.

---

Example 3: Per-Department Metrics with GROUP BY (Intermediate)

SELECT
  department,
  COUNT(*) AS employee_count,
  COUNT(salary) AS salaried_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY department;

Expected output:

 department | employee_count | salaried_count | avg_salary
------------+----------------+---------------+--------------------
 HR         | 2              | 2             | 41000.000000000000
 IT         | 1              | 0             |
 Sales      | 2              | 2             | 55000.000000000000
(3 rows)

Explanation:

• AVG(salary) for IT is NULL because there are no non-NULL salary values.

---

Example 4: Conditional Aggregates with FILTER (Advanced)

PostgreSQL supports FILTER to compute conditional aggregates without duplicating whole queries.

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status      text NOT NULL,
  amount      numeric(12,2)
);

INSERT INTO orders (customer_id, status, amount) VALUES
  (10, 'paid',  25.00),
  (10, 'paid',  10.00),
  (10, 'failed', 5.00),
  (11, 'paid',  50.00),
  (12, 'pending', NULL);

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue,
  AVG(amount) FILTER (WHERE status = 'paid') AS paid_aov
FROM orders;

Expected output:

 total_orders | paid_orders | paid_revenue | paid_aov
--------------+------------+--------------+--------------------
 5            | 3          | 85.00        | 28.333333333333332
(1 row)

Explanation:

• FILTER applies the condition only to the aggregate it is attached to.
• NULL amounts are ignored by SUM/AVG even inside the filter.

---

Example 5: Avoiding Double Counting After Joins (Advanced)

If a join multiplies rows, your aggregates can be wrong.

CREATE TABLE order_items (
  item_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  qty      integer NOT NULL CHECK (qty >= 0)
);

INSERT INTO order_items (order_id, qty) VALUES
  (1, 1),
  (1, 2),
  (2, 1);

-- WRONG: amount is repeated once per item row
SELECT SUM(o.amount) AS inflated_revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid';

Bad outcome:

• Order 1 appears twice (two items), so its amount is summed twice.

Fix (aggregate orders first, then join if needed):

WITH paid_orders AS (
  SELECT order_id, amount
  FROM orders
  WHERE status = 'paid'
)
SELECT SUM(amount) AS paid_revenue
FROM paid_orders;

Expected output (paid_revenue):

 paid_revenue
-------------
 85.00
(1 row)

---

Practical Use Cases

1) Revenue and order volume

SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue
FROM orders
WHERE status = 'paid';

2) Average order value (AOV)

SELECT AVG(amount) AS aov
FROM orders
WHERE status = 'paid';

3) Monthly signups

SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

4) Inventory totals

SELECT SUM(quantity_on_hand) AS total_stock
FROM inventory;

5) Conditional metrics with FILTER

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed
FROM jobs;

---

Common Mistakes & Troubleshooting

1) Using COUNT(col) when you mean COUNT(*)

Wrong SQL:

SELECT COUNT(salary) AS employees
FROM employees;

Bad outcome:

• Excludes NULL salaries.

Fix:

SELECT COUNT(*) AS employees
FROM employees;

---

2) Forgetting GROUP BY

Wrong SQL:

SELECT department, AVG(salary)
FROM employees;

Typical error:

ERROR:  column "employees.department" must appear in the GROUP BY clause or be used in an aggregate function

Fix:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

---

3) Treating NULL as zero implicitly

Wrong assumption:

• expecting SUM(amount) to treat missing amounts as 0.

Fix (only if your business meaning is “missing means 0”):

SELECT SUM(COALESCE(amount, 0))
FROM orders;

---

4) Double counting after joins

Wrong SQL:

SELECT SUM(o.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;

Bad outcome:

• Revenue is inflated when orders have multiple items.

Fix:

SELECT SUM(amount)
FROM orders;

Or aggregate to one row per order before joining.

---

Debugging checklist:

1. Decide whether you want rows (COUNT(*)) or values (COUNT(col)).
2. Check for NULLs in the aggregated column.
3. Validate join multiplicity (does the join multiply rows?).
4. For conditional metrics, consider FILTER.
5. Sanity-check results on a small slice with WHERE + LIMIT.

---

Best Practices

• ✅ Alias aggregate outputs (SUM(amount) AS total_revenue). ❌ Avoid unlabeled aggregates in reports.
• ✅ Prefer COUNT(*) for row counts. ❌ Avoid COUNT(col) unless you specifically want to ignore NULL.
• ✅ Use FILTER for conditional aggregates in PostgreSQL. ❌ Avoid duplicating queries with slightly different WHERE clauses.
• ✅ Guard against join multiplication by pre-aggregating. ❌ Avoid aggregating after joins unless you understand cardinality.
• ✅ Index columns used in WHERE for large aggregations. ❌ Avoid full-table scans in high-frequency dashboards.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_sales (
  sale_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product  text NOT NULL,
  quantity integer,
  price    numeric(10,2) NOT NULL
);

INSERT INTO practice_sales (product, quantity, price) VALUES
  ('Book', 3, 15.00),
  ('Pen', 10, 1.50),
  ('Notebook', 5, 7.25),
  ('Book', 2, 15.00),
  ('Pen', NULL, 1.50);

Exercise 1 (Easy): Count transactions

Task: Count the total number of sales transactions.

-- Your SQL here

Solution:

SELECT COUNT(*) AS total_transactions
FROM practice_sales;

---

Exercise 2 (Medium): Average quantity (ignore NULLs)

Task: Compute the average quantity (NULL quantities should be ignored).

-- Your SQL here

Solution:

SELECT AVG(quantity) AS avg_quantity
FROM practice_sales;

---

Exercise 3 (Advanced): Total revenue per product

Task: Compute revenue per product as SUM(quantity * price) grouped by product.

-- Your SQL here

Solution:

SELECT
  product,
  SUM(quantity * price) AS total_revenue
FROM practice_sales
GROUP BY product
ORDER BY product;

---

Connection to Other Concepts

Concept	Why it matters
MIN/MAX	other aggregates often used alongside these metrics
GROUP BY	produces per-group metrics
HAVING	filters based on aggregate results
NULL Values	explains ignored NULL behavior in AVG/SUM
Joins	join multiplicity affects aggregates
Window functions	compute per-row metrics using aggregates over windows

---

Visual Learning Diagram

flowchart TD
  A[SELECT] --> B[Aggregates]
  B --> C[COUNT]
  B --> D[SUM]
  B --> E[AVG]
  B --> F[GROUP BY]
  F --> G[Per-Group Metrics]
  B --> H[FILTER]
  B --> I[Join Multiplicity]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using COUNT(col) expecting row counts	missing NULL rows	use COUNT(*)
Ignoring join multiplication	inflated metrics	pre-aggregate or join on unique keys
Treating missing values as zeros accidentally	misleading averages/totals	use COALESCE only when business rules justify it
Forgetting to group	errors or wrong results	GROUP BY all non-aggregated columns
Unbounded dashboard queries	slow/expensive reads	filter by time window and index predicate columns

---

Quick Reference

SELECT COUNT(*) FROM t;
SELECT COUNT(col) FROM t;
SELECT COUNT(DISTINCT col) FROM t;
SELECT SUM(col) FILTER (WHERE cond) FROM t;
SELECT AVG(col) FROM t;

What's Next

• Previous: MIN(), MAX() - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.