SQL COUNT, AVG and SUM Functions: Aggregate Data (Examples)

SQL Tutorial · Chapter 16 of 32

The SQL COUNT, AVG and SUM functions are aggregate functions that summarise many rows into a single number. COUNT tells you how many rows (or non-NULL values) there are, SUM adds up a numeric column and AVG returns its mean. Together with MIN and MAX they are the foundation of every report, KPI card and dashboard built on SQL.

SQL COUNT, AVG and SUM Syntax

SELECT COUNT(*) FROM table_name;            -- all rows
SELECT COUNT(column_name) FROM table_name;  -- non-NULL values only
SELECT COUNT(DISTINCT column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT AVG(column_name) FROM table_name;

Sample Table

Example 1: COUNT Rows

SELECT COUNT(*) AS TotalOrders FROM Orders;

COUNT(*) counts every row regardless of NULLs. COUNT(Amount) would count only rows where Amount is not NULL, which here is also 6.

Example 2: SUM a Column

SELECT SUM(Amount) AS TotalRevenue FROM Orders;

Example 3: AVG a Column

SELECT ROUND(AVG(Amount), 2) AS AverageOrder FROM Orders;

2800 divided by 6 is 466.666…, so ROUND keeps the output tidy. AVG ignores NULL rows entirely: they are excluded from both the total and the count.

Example 4: Several Aggregates with a Filter

SELECT COUNT(*) AS Orders, SUM(Amount) AS Revenue, AVG(Amount) AS AvgAmount
FROM Orders
WHERE Product = 'Laptop';

Example 5: COUNT DISTINCT

SELECT COUNT(DISTINCT CustomerID) AS Buyers, COUNT(*) AS Orders
FROM Orders;

Customer 1 placed two orders, so six orders came from five different customers.

Example 6: Aggregates per Group

SELECT Product, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM Orders
GROUP BY Product
ORDER BY Revenue DESC;

COUNT(*) vs COUNT(column)

Works in MySQL, SQL Server, PostgreSQL, SQLite

• All three functions are standard and skip NULLs in all four databases. SUM and AVG over zero rows return NULL, not 0; wrap in COALESCE(SUM(Amount), 0) if you need a zero.
• Integer AVG: SQL Server truncates the average of an integer column to an integer (466 here). Cast first: AVG(CAST(Amount AS DECIMAL(10,2))). MySQL, PostgreSQL and SQLite return a decimal.
• SQL Server’s COUNT returns a 32-bit integer; use COUNT_BIG for more than two billion rows.

Choosing the Right Aggregate

COUNT answers “how many”, SUM answers “how much in total” and AVG answers “how much per item”. Mixing them up produces plausible but wrong numbers: summing a Quantity column gives units sold, while counting the same rows gives the number of order lines, and the two differ as soon as any line has a quantity above one. Averages also hide distribution: an AVG of 466 across six orders says nothing about the fact that two laptops make up 84 percent of revenue. Pair AVG with MIN, MAX and COUNT so the reader can judge how representative it is.

Aggregates can also be nested inside expressions. SUM(Amount) / COUNT(DISTINCT CustomerID) gives revenue per buyer, and SUM(CASE WHEN Product = 'Laptop' THEN Amount ELSE 0 END) gives a conditional total without a separate query.

Common Mistakes

• Mixing aggregated and plain columns without GROUP BY, e.g. SELECT Product, SUM(Amount) FROM Orders.
• Treating NULL as zero in AVG. Three values 10, 20 and NULL average to 15, not 10.
• Applying SUM to a text column or to a column that stores codes rather than quantities.
• Filtering aggregates with WHERE instead of HAVING.

Practice Exercise

1. What is the total Amount of all orders placed in January 2026?
2. How many orders and what average Amount did customer 1 place?

-- 1
SELECT SUM(Amount) AS JanRevenue FROM Orders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31';
-- 1525

-- 2
SELECT COUNT(*) AS Orders, AVG(Amount) AS AvgAmount FROM Orders WHERE CustomerID = 1;
-- 2, 750

Related Chapters

• SQL MIN and MAX – the other two aggregate functions
• SQL GROUP BY – aggregates per category
• SQL NULL Values – how NULL affects counts and averages
• SQL from Scratch: full course overview

FAQ

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not NULL, so it can be smaller.

Does AVG include NULL values?

No. Rows with NULL are excluded from both the sum and the divisor, so the average reflects only real values.

Why does SUM return NULL instead of 0?

When no rows match the filter there is nothing to add, so SUM returns NULL. Use COALESCE(SUM(column), 0) to display zero.

Chapter 16 of 32 · SQL from Scratch: all 32 chapters