SQLScalar and Aggregate Functionsbeginner45 min read

Aggregate Functions: COUNT, SUM, AVG, MIN, and MAX

Master SQL aggregate functions to count rows, calculate totals and averages, find minimum and maximum values, handle NULLs correctly, and generate useful business statistics from database tables.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain how aggregate functions summarize values across multiple rows.
✓Use COUNT(*) to count rows and COUNT(column) to count non-NULL values.
✓Calculate totals using SUM() and arithmetic means using AVG().
✓Find minimum and maximum values using MIN() and MAX().
✓Use COUNT(DISTINCT column) to count unique non-NULL values.
✓Understand how aggregate functions behave with NULL values and empty input sets.
✓Combine aggregate functions with WHERE and introduce GROUP BY.
✓Recognize common mistakes involving NULLs, numeric precision, and aggregation.

1. What Are Aggregate Functions?

Imagine a table containing 100 orders. You want to know how many orders exist, how much money they represent, the average order value, and the cheapest and most expensive orders.

You could retrieve all 100 rows and calculate everything manually. SQL aggregate functions let the database perform these calculations for you.

An aggregate function takes values from multiple rows and produces a summary value. When an aggregate query contains no GROUP BY and operates on an ordinary table, it commonly returns one summary row, even when no rows match the filter.

The five core aggregate functions in this lesson are COUNT(), SUM(), AVG(), MIN(), and MAX().

A Simple Aggregate Query

sql

1
2
3
4
5
SELECT
  COUNT(*) AS total_products,
  MIN(price) AS cheapest_price,
  MAX(price) AS highest_price
FROM products;

Output

One summary row containing the number of products, the minimum price, and the maximum price. The actual values depend on the products table.

COUNT() counts rows, while MIN() and MAX() calculate the smallest and largest non-NULL price values.

2. Understanding the Example Dataset

Throughout this lesson, use the following small orders dataset to understand how each aggregate function works. The table is illustrative; your actual database may contain different records.

Assume the orders table has these columns: order_id, customer_id, total_amount, and status.

Example orders

order_id	customer_id	total_amount	status
1	101	100.00	completed
2	101	200.00	completed
3	102	NULL	processing
4	103	50.00	completed
5	NULL	150.00	completed

3. COUNT(*) — Count Rows

COUNT(*) counts every row in the input set. It does not matter whether one or more columns in a row contain NULL.

Use COUNT(*) when you want the number of rows that satisfy your query's filters.

For example, if an orders table contains five rows and you do not apply a filter, COUNT(*) returns 5.

Count All Orders

sql

1
2
3
SELECT
  COUNT(*) AS total_orders
FROM orders;

Output

total_orders ------------ 5

All five example rows are counted, including the row whose total_amount is NULL and the row whose customer_id is NULL.

4. COUNT(column) — Count Non-NULL Values

COUNT(column) counts the number of rows in which that column's value is not NULL. It does not count every row.

In the example dataset, there are five orders, but one order has a NULL total_amount. Therefore, COUNT(total_amount) returns 4.

This is useful when you want to measure how many records have a populated value in a particular field.

Compare Total Orders and Orders With Amounts

sql

1
2
3
4
SELECT
  COUNT(*) AS total_orders,
  COUNT(total_amount) AS orders_with_amount
FROM orders;

Output

total_orders | orders_with_amount -------------+------------------- 5 | 4

COUNT(*) counts all five rows. COUNT(total_amount) ignores the one row where total_amount is NULL.

5. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

These three expressions answer different questions. Understanding their differences is essential for accurate reporting and interviews.

COUNT variations

Expression	What it counts	Example result
COUNT(*)	All rows in the input set	5 orders
COUNT(customer_id)	Rows with a non-NULL customer_id	4 orders
COUNT(DISTINCT customer_id)	Unique non-NULL customer IDs	3 customers
COUNT(total_amount)	Rows with a non-NULL total_amount	4 orders

6. COUNT(DISTINCT column) — Count Unique Values

DISTINCT removes duplicate values from the set being counted. COUNT(DISTINCT column) then counts the remaining unique, non-NULL values.

In the example data, customer 101 appears twice, customer 102 and 103 appear once each, and one order has no customer ID. There are three unique non-NULL customer IDs.

This is useful for metrics such as unique purchasing customers, unique products sold, and unique visitors represented in a dataset.

Count Unique Purchasing Customers

sql

1
2
3
SELECT
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Output

unique_customers ---------------- 3

The duplicate customer_id 101 is counted once. The NULL customer_id is not counted.

7. SUM() — Calculate a Total

SUM(column) adds the non-NULL numeric values in a column. It ignores NULL values instead of treating them as zero for purposes of including them in the input.

In the example dataset, the non-NULL amounts are 100, 200, 50, and 150. Their sum is 500.

SUM() is useful for revenue totals, inventory quantities, expenses, and other numeric measurements. The column must have a data type that supports the required addition operation.

Calculate Total Order Amount

sql

1
2
3
SELECT
  SUM(total_amount) AS total_order_amount
FROM orders;

Output

total_order_amount ------------------ 500.00

The four available numeric amounts are added together. The NULL amount is ignored. The displayed decimal formatting is illustrative.

8. AVG() — Calculate an Average

AVG(column) calculates the arithmetic mean of the non-NULL input values. It adds those values and divides by the number of non-NULL values.

For the example amounts 100, 200, 50, and 150:

Average = (100 + 200 + 50 + 150) / 4 = 125.

The NULL amount does not count as a value in the average. This distinction matters because dividing the sum by all five rows would produce a different result.

In PostgreSQL, AVG() returns a result type determined by the input type. For integer inputs, PostgreSQL returns a numeric result, avoiding ordinary integer truncation for AVG(integer). Other database systems may have different type rules.

Calculate Average Order Amount

sql

1
2
3
SELECT
  AVG(total_amount) AS average_order_amount
FROM orders;

Output

average_order_amount -------------------- 125.0000000000000000

The average uses the four non-NULL amounts. The exact displayed scale depends on the database client and result type.

9. ROUND() — Format Numeric Results

Aggregate calculations can produce values with many decimal places. PostgreSQL's ROUND() function can round a numeric result to a specified number of decimal places.

For currency-style reporting, ROUND(AVG(total_amount), 2) requests two decimal places. Rounding changes the displayed or returned numeric value; it does not change the original values stored in the table.

PostgreSQL supports ROUND(numeric, integer). For floating-point inputs, available overloads and precision behavior differ, so use an appropriate numeric type when exact decimal calculations are important.

Average Rounded to Two Decimal Places

sql

1
2
3
SELECT
  ROUND(AVG(total_amount), 2) AS average_order_amount
FROM orders;

Output

average_order_amount -------------------- 125.00

ROUND() rounds the average to two decimal places. The exact result depends on the values in the database.

10. MIN() — Find the Smallest Value

MIN(column) returns the smallest non-NULL value in the input set.

For numbers, it returns the lowest number. For dates and timestamps, it returns the earliest value. For strings, it returns the value that sorts first under the applicable collation.

MIN() is useful for finding the lowest product price, earliest order date, or smallest recorded measurement.

Find the Cheapest Order

sql

1
2
3
SELECT
  MIN(total_amount) AS smallest_order
FROM orders;

Output

smallest_order -------------- 50.00

The smallest non-NULL amount in the example dataset is 50.

11. MAX() — Find the Largest Value

MAX(column) returns the largest non-NULL value in the input set.

It works with numbers, dates, timestamps, and other comparable types. For dates, MAX() can identify the latest date in the input set.

MIN() and MAX() do not return the entire row associated with the minimum or maximum. They return the value itself. Retrieving the full row associated with an extreme value requires additional query logic.

Find the Largest Order

sql

1
2
3
SELECT
  MAX(total_amount) AS largest_order
FROM orders;

Output

largest_order ------------- 200.00

The largest non-NULL amount in the example dataset is 200.

12. Using All Five Aggregate Functions Together

You can combine several aggregate functions in a single SELECT statement. Each function calculates a different statistic over the same filtered input set.

This is useful for summary dashboards because the database can calculate several related metrics in one query.

Complete Order Summary

sql

1
2
3
4
5
6
7
8
9
SELECT
  COUNT(*) AS total_orders,
  COUNT(customer_id) AS orders_with_customer,
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(total_amount) AS total_amount,
  ROUND(AVG(total_amount), 2) AS average_amount,
  MIN(total_amount) AS smallest_amount,
  MAX(total_amount) AS largest_amount
FROM orders;

Output

One row with the total order count, orders with a customer ID, unique customers, sum, average, minimum, and maximum. Using the example dataset, the counts are 5, 4, and 3; the sum is 500; the average is 125; the minimum is 50; and the maximum is 200.

Each aggregate summarizes the rows independently. NULL handling differs between COUNT(*) and the other expressions.

13. Using WHERE Before Aggregation

WHERE filters rows before the aggregate functions calculate their results. Therefore, an aggregate query with a WHERE condition summarizes only the rows that pass that condition.

For example, a report for completed orders should filter by status before calculating revenue and average order value.

In the example dataset, four orders are completed. One of those four has a NULL amount, so COUNT(*) returns 4 while COUNT(total_amount) returns 3.

Summarize Completed Orders

sql

1
2
3
4
5
6
7
SELECT
  COUNT(*) AS completed_orders,
  COUNT(total_amount) AS orders_with_amount,
  SUM(total_amount) AS completed_order_total,
  AVG(total_amount) AS average_completed_order
FROM orders
WHERE status = 'completed';

Output

Using the example dataset: completed_orders = 4, orders_with_amount = 3, completed_order_total = 500, and average_completed_order = 166.6666666666666667 approximately.

The processing order is important: WHERE removes non-matching rows first, then aggregate functions summarize the remaining rows.

14. What Happens When Values Are NULL?

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or FALSE.

For the aggregate functions in this lesson:

- COUNT(*) counts rows even when their columns contain NULL.
- COUNT(column) ignores rows where that expression is NULL.
- SUM(column) adds non-NULL values.
- AVG(column) averages non-NULL values.
- MIN(column) and MAX(column) ignore NULL values.

Do not automatically replace missing values with zero. Whether a missing amount should count as zero is a business rule, not a formatting decision.

Inspect NULL Handling

sql

1
2
3
4
5
6
SELECT
  COUNT(*) AS row_count,
  COUNT(total_amount) AS amount_count,
  SUM(total_amount) AS amount_sum,
  AVG(total_amount) AS amount_average
FROM orders;

Output

For the example dataset: row_count = 5, amount_count = 4, amount_sum = 500, and amount_average = 125.

The row with NULL total_amount contributes to COUNT(*) but not to COUNT(total_amount), SUM(total_amount), or AVG(total_amount).

15. Aggregate Functions on Empty Input

A common edge case occurs when no rows match a filter.

In a PostgreSQL aggregate query without GROUP BY, COUNT(*) returns 0 when the input contains no rows. SUM(), AVG(), MIN(), and MAX() return NULL because there are no non-NULL values to calculate from.

This means an empty result set and a summary result containing NULL values are not the same thing. The aggregate query can still return one summary row even though no source rows matched.

If an application needs zero instead of NULL for a total, COALESCE() can be used deliberately. Only do this when zero is the correct meaning for the report.

Aggregate When Nothing Matches

sql

1
2
3
4
5
6
7
8
9
SELECT
  COUNT(*) AS matching_orders,
  SUM(total_amount) AS total_amount,
  COALESCE(SUM(total_amount), 0) AS total_amount_or_zero,
  AVG(total_amount) AS average_amount,
  MIN(total_amount) AS minimum_amount,
  MAX(total_amount) AS maximum_amount
FROM orders
WHERE status = 'status_that_does_not_exist';

Output

matching_orders = 0, total_amount = NULL, total_amount_or_zero = 0, average_amount = NULL, minimum_amount = NULL, maximum_amount = NULL.

COUNT returns zero for the empty input. Other aggregates return NULL. COALESCE explicitly replaces the NULL sum with zero.

16. Introducing GROUP BY

So far, each aggregate query has summarized the entire filtered input set. GROUP BY lets you divide that input into groups and calculate a separate summary for each group.

Imagine you want to know the total revenue for each order status. GROUP BY status creates a group for each status value, and SUM(total_amount) calculates the total within each group.

When a query selects a regular column alongside an aggregate, that column generally needs to be part of the GROUP BY or be used in another valid expression. PostgreSQL also recognizes certain functional dependencies, but explicit grouping is the clearest approach for beginners.

Total Orders by Status

sql

1
2
3
4
5
6
7
SELECT
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY status;

Output

One row per status group, showing the number of orders and sum of non-NULL amounts in each group. A NULL status, if present, forms its own group.

GROUP BY produces one result row per group rather than one row for the entire table.

17. Why Unaggregated Columns Need Grouping

Consider this query:

SELECT status, AVG(total_amount) FROM orders;

The query asks for one average across the input set, but also asks for a status value. If multiple statuses exist, there is no single status that naturally represents that overall average.

A common solution is to group by status. Another valid option is to remove status if you want one overall average. Do not add GROUP BY automatically without first deciding what question the report should answer.

Incorrect and Correct Aggregation

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Incorrect in PostgreSQL: status is not grouped
-- and is not aggregated.
SELECT
  status,
  AVG(total_amount)
FROM orders;
 
-- Correct: one average per status.
SELECT
  status,
  AVG(total_amount) AS average_amount
FROM orders
GROUP BY status;
 
-- Also correct: one average across all orders.
SELECT
  AVG(total_amount) AS average_amount
FROM orders;

Output

The first query is rejected by PostgreSQL for this schema. The second returns one row per status. The third returns one overall average.

The selected columns must make sense at the same grouping level. Grouping by status changes the meaning of the calculation.

18. Aggregate Functions and Data Types

The input data type influences the result type and the precision of aggregate calculations.

For financial values in PostgreSQL, NUMERIC or DECIMAL is often appropriate when exact decimal arithmetic is required. Floating-point types are approximate representations and can introduce rounding differences.

AVG() and division should be understood in the context of the database's type rules. PostgreSQL returns numeric for AVG(integer), but other database systems may handle integer averages differently. ROUND() also has different overloads depending on the input type.

Do not convert an amount to a rounded value before aggregation unless the business requirement explicitly calls for that. Rounding each row first can produce a different total from summing the original values and rounding the final result.

Round the Final Total

sql

1
2
3
4
SELECT
  SUM(total_amount) AS unrounded_total,
  ROUND(SUM(total_amount), 2) AS total_to_two_decimals
FROM orders;

Output

Returns the sum of non-NULL amounts and the same sum rounded to two decimal places. The actual values depend on the data.

This example rounds the final aggregate result. Rounding each row before summing is a different calculation and may yield a different result.

19. COUNT(DISTINCT ...) and NULL

COUNT(DISTINCT column) counts unique non-NULL values. It does not count NULL as a distinct customer or category.

For example, if customer_id contains 101, 101, 102, NULL, and NULL, COUNT(DISTINCT customer_id) returns 2.

If you need to measure how many rows have a missing customer ID, use COUNT(*) with a suitable IS NULL filter. Do not confuse unique populated values with the number of rows missing data.

Count Missing Customer IDs

sql

1
2
3
4
SELECT
  COUNT(*) AS orders_without_customer
FROM orders
WHERE customer_id IS NULL;

Output

Returns the number of orders whose customer_id is NULL. For the example dataset, the result is 1.

COUNT(*) counts every row that passes WHERE. The filter selects only rows with a missing customer ID.

20. Aggregate Functions and Query Performance

Aggregate functions may need to inspect many qualifying rows. The actual work depends on the table size, filters, indexes, data distribution, and execution plan.

An index can help a query find qualifying rows, and some queries can use index-only scans when the index contains the needed values and PostgreSQL's visibility requirements are satisfied. However, an index does not guarantee that an aggregate query will be faster.

For performance-sensitive queries, inspect the plan with EXPLAIN. Use EXPLAIN ANALYZE only when you understand that it executes the query, which may have side effects for statements that modify data.

Do not assume COUNT(*) is a constant-time operation over an ordinary PostgreSQL table. An exact count may require examining a large amount of table or index data.

Inspect an Aggregate Query Plan

sql

1
2
3
4
5
6
EXPLAIN
SELECT
  COUNT(*) AS completed_orders,
  SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'completed';

Output

PostgreSQL displays a query plan describing how it expects to execute the aggregate query. The exact plan depends on the schema, indexes, statistics, and data.

EXPLAIN helps you inspect the planned operations. It does not guarantee that a particular index will be used.

21. Guided Practice

Use the example orders table from this lesson. Write each query before looking at the solutions.

Task 1: Count every row in orders.

Task 2: Count orders that have a non-NULL customer_id.

Task 3: Count unique non-NULL customer IDs.

Task 4: Calculate the sum and average of total_amount.

Task 5: Find the minimum and maximum total_amount.

Task 6: Count completed orders and calculate their total amount.

Task 7: Count orders whose total_amount is NULL.

Task 8: Calculate the total amount for each status using GROUP BY.

Task 9: Explain why COUNT(*) and COUNT(total_amount) can return different results.

Task 10: Explain why SUM(total_amount) can return NULL when no rows match the filter.

22. Guided Practice Solutions

Compare these solutions with your answers. They use PostgreSQL syntax and the illustrative schema from the lesson.

Tasks 1–3: Counting

sql

1
2
3
4
5
6
7
8
9
10
11
-- Task 1: Count every row.
SELECT COUNT(*) AS total_orders
FROM orders;
 
-- Task 2: Count non-NULL customer IDs.
SELECT COUNT(customer_id) AS orders_with_customer
FROM orders;
 
-- Task 3: Count unique non-NULL customer IDs.
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Output

For the example dataset, the results are 5 total orders, 4 orders with a customer ID, and 3 unique customers.

Each expression answers a different question about the rows and their values.

23. More Practice Solutions

The next examples cover numerical summaries, filtering, missing data, and grouped calculations.

Tasks 4–7: Summaries and NULLs

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Task 4: Sum and average.
SELECT
  SUM(total_amount) AS total_amount,
  AVG(total_amount) AS average_amount
FROM orders;
 
-- Task 5: Minimum and maximum.
SELECT
  MIN(total_amount) AS minimum_amount,
  MAX(total_amount) AS maximum_amount
FROM orders;
 
-- Task 6: Completed orders.
SELECT
  COUNT(*) AS completed_orders,
  SUM(total_amount) AS completed_total
FROM orders
WHERE status = 'completed';
 
-- Task 7: Count missing amounts.
SELECT COUNT(*) AS orders_without_amount
FROM orders
WHERE total_amount IS NULL;

Output

Using the example dataset: the overall sum is 500, average is 125, minimum is 50, maximum is 200, completed orders count is 4, completed total is 500, and orders without an amount count is 1.

SUM and AVG ignore NULL amounts, while COUNT(*) counts every row that passes the filter.

24. GROUP BY Practice Solution

Task 8 asks for a summary for each order status. GROUP BY creates one group per status value.

Task 8: Totals by Status

sql

1
2
3
4
5
6
7
SELECT
  status,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY status;

Output

Returns one row per status. With the example dataset, completed has 4 orders and a total of 500, while processing has 1 order and a NULL total because its only amount is NULL.

SUM returns NULL for a group with no non-NULL amount values. If the business rule requires zero in such cases, use COALESCE(SUM(total_amount), 0) explicitly.

25. Practice Explanations

Task 9: COUNT(*) counts rows regardless of whether total_amount is NULL. COUNT(total_amount) counts only rows with a non-NULL amount. If one row has a NULL amount, the counts differ by one for that input set.

Task 10: SUM(total_amount) returns NULL when the input set has no non-NULL amount values. This can happen when no rows match a WHERE filter or when matching rows exist but every amount is NULL. COUNT(*) can distinguish an empty input set from an input set containing rows with missing amounts.

26. Multiple-Choice Questions

Choose the best answer for each question. The answer key includes a short explanation.

27. SQL Interview Questions and Answers

Q1. What is an aggregate function?
A: A function that summarizes values across multiple rows, such as COUNT, SUM, AVG, MIN, or MAX.

Q2. What is COUNT(*)?
A: It counts all rows in the input set, regardless of NULL values in individual columns.

Q3. What is COUNT(column)?
A: It counts rows where the specified expression is not NULL.

Q4. What is the difference between COUNT(*) and COUNT(column)?
A: COUNT(*) counts rows; COUNT(column) counts non-NULL values of that expression.

Q5. What does COUNT(DISTINCT column) do?
A: It counts unique non-NULL values of the expression.

Q6. Does COUNT(DISTINCT column) count NULL as one distinct value?
A: No. COUNT excludes NULL values, including after DISTINCT is applied.

Q7. How does SUM() handle NULL values?
A: It ignores NULL inputs and sums the remaining non-NULL values.

Q8. How does AVG() handle NULL values?
A: It calculates the average from non-NULL inputs only.

Q9. What do MIN() and MAX() do?
A: They return the smallest and largest non-NULL values, respectively.

Q10. Can MIN() and MAX() work with dates?
A: Yes. They can return the earliest and latest non-NULL date or timestamp values.

Q11. What happens when SUM() receives no non-NULL values?
A: In PostgreSQL, it returns NULL.

Q12. What does COUNT(*) return for an empty input set without GROUP BY?
A: It returns zero in the aggregate result row.

Q13. What is GROUP BY used for?
A: It divides rows into groups so aggregates can return a separate result for each group.

Q14. Why can you not normally select an ungrouped column alongside an aggregate?
A: The database cannot arbitrarily choose one value for that column to represent the group. The column must be grouped or otherwise validly determined.

Q15. What is the difference between WHERE and GROUP BY?
A: WHERE filters input rows before aggregation; GROUP BY defines how the remaining rows are grouped.

Q16. How would you count orders without a customer ID?
A: SELECT COUNT(*) FROM orders WHERE customer_id IS NULL;

Q17. How would you calculate average order value?
A: Use AVG(total_amount), applying any required business filters and rounding rules.

Q18. Why should financial calculations use suitable numeric types?
A: Exact decimal types such as NUMERIC in PostgreSQL can avoid the approximation inherent in floating-point representations.

Q19. Does COUNT(*) always use a constant-time row counter?
A: No. Exact counts on ordinary PostgreSQL tables may require examining many rows or index entries, depending on the query and execution plan.

Q20. Does an index always speed up an aggregate query?
A: No. Index usefulness depends on the filter, data distribution, required columns, table size, and execution plan.

Q21. What is COALESCE(SUM(amount), 0) used for?
A: It returns the sum when non-NULL and zero when the sum is NULL. Use it only when zero is the correct business interpretation.

Q22. Does SUM(amount) count NULL amounts as zero?
A: It ignores them. The result may match a calculation that treats missing amounts as zero, but that does not mean the missing values were actually interpreted as zero.

Q23. How do you calculate a total for each department?
A: SELECT department, SUM(amount) FROM records GROUP BY department, adapting the table and column names to the schema.

Q24. Why can the average differ when missing values are replaced with zero?
A: Replacing NULL with zero adds those rows to the average's effective input, changing the denominator and possibly the sum.

Q25. What is the difference between aggregation and sorting?
A: Aggregation summarizes values into results; ORDER BY sorts the result rows.

28. Lesson Summary and Final Checklist

You have learned how to summarize database records using the five core aggregate functions.

COUNT(*) counts rows. COUNT(column) counts non-NULL values. COUNT(DISTINCT column) counts unique non-NULL values. SUM() calculates a total, AVG() calculates an arithmetic mean, and MIN() and MAX() find the smallest and largest non-NULL values.

Remember these important rules:

- NULL is not the same as zero.
- WHERE filters rows before aggregation.
- GROUP BY creates separate aggregate results for each group.
- In PostgreSQL, COUNT returns zero for an empty input set, while SUM, AVG, MIN, and MAX return NULL when there are no non-NULL values to aggregate.
- ROUND() can round numeric results, but the correct rounding stage depends on the business requirement.
- Indexes can help some aggregate queries, but performance must be evaluated rather than assumed.

Before moving on, make sure you can write a query that counts all orders, counts populated customer IDs, counts unique customers, calculates total and average revenue, finds minimum and maximum values, and summarizes totals by status.

29. Common Mistakes

Review these mistakes to avoid incorrect statistics and confusing aggregate queries.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

Aggregate functions summarize values across a set of rows.
COUNT(*) counts rows, including rows containing NULL values.
COUNT(column) counts only rows where the specified expression is not NULL.
COUNT(DISTINCT column) counts unique non-NULL values.
SUM(), AVG(), MIN(), and MAX() ignore NULL inputs.
Except for COUNT, aggregate functions generally return NULL when there are no input values to aggregate.
WHERE filters rows before aggregation.
GROUP BY divides rows into groups so aggregate functions can produce a result for each group.
Integer division and numeric precision can affect average calculations.
Aggregate results describe the rows that actually reach the aggregate, not necessarily every row in the original table.

Was this lesson helpful?

Found an error? Report an issue