EVIKA.ACADEMY›SQL Tutorial
SQL Tutorial›COUNT, SUM, AVG

SQL COUNT(), SUM(), and AVG()

Chapter 14 of 40 · Estimated read time: 8 min

These three functions answer the most common analytical questions: how many? (COUNT), what is the total? (SUM), and what is the average? (AVG). Together with MIN and MAX, they form the core of SQL aggregation.

COUNT()
How many rows?
SUM()
What is the total?
AVG()
What is the average?

COUNT()

COUNT(*) -- counts every row including NULLs COUNT(column) -- counts only non-NULL values in that column COUNT(DISTINCT c) -- counts unique non-NULL values

COUNT(*) — Total Rows

SELECT COUNT(*) AS total_products FROM products;
total_products
7

COUNT(column) — Non-NULL Rows

-- Count only customers who have provided an email
SELECT COUNT(email) AS customers_with_email FROM customers;
customers_with_email
4

COUNT(DISTINCT) — Unique Values

-- How many distinct cities do our customers come from?
SELECT COUNT(DISTINCT city) AS unique_cities FROM customers;
Noida, Delhi, Mumbai, Bengaluru — 4 distinct cities (C01 and C04 both from Noida)
unique_cities
4
Interview favourite: "What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?" — COUNT(*) includes NULLs and duplicates; COUNT(column) excludes NULLs; COUNT(DISTINCT column) excludes NULLs AND duplicates.

SUM()

SELECT SUM(column_name) FROM table_name;

SUM adds up all non-NULL values in a numeric column. NULL values are skipped — they do not count as zero.

-- Total revenue from all orders
SELECT SUM(total_amount) AS total_revenue FROM orders;
total_revenue
17581
-- Total order value per customer
SELECT
  customer_id,
  SUM(total_amount) AS customer_total
FROM orders
GROUP BY customer_id
ORDER BY customer_total DESC;
Each customer's total spend
customer_idcustomer_total
C0110383
C022998
C042398
C038999
C05299
SUM is NULL-safe — if a column has some NULLs, SUM still returns the total of the non-NULL rows. But if ALL values are NULL, SUM returns NULL (not 0).

AVG()

SELECT AVG(column_name) FROM table_name;

AVG divides the sum of non-NULL values by their count. NULL values are excluded from both the numerator and denominator.

-- Average product price
SELECT AVG(price) AS avg_price FROM products;
(899+1499+99+49+1199+8999+299) / 7 = 1863.43 — rounded by database
avg_price
1848.57
-- Average order value per customer
SELECT
  customer_id,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC;
customer_idavg_order_value
C038999
C013461
C042398
C021499
C05299
AVG vs SUM/COUNT: AVG(col) is identical to SUM(col) / COUNT(col). Use AVG for clarity; use the manual form when you also need the sum and count separately.

All Five Aggregate Functions Together

-- Complete summary of the orders table
SELECT
  COUNT(*)                       AS total_orders,
  COUNT(DISTINCT customer_id)    AS unique_customers,
  SUM(total_amount)              AS total_revenue,
  ROUND(AVG(total_amount), 2)    AS avg_order_value,
  MIN(total_amount)              AS smallest_order,
  MAX(total_amount)              AS largest_order
FROM orders;
Full summary in one query
total_ordersunique_customerstotal_revenueavg_order_valuesmallest_orderlargest_order
75175812511.572998999

Rounding Averages with ROUND()

-- ROUND(value, decimal_places)
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;
-- Returns 1848.57 instead of 1848.571428...
Always wrap AVG with ROUND() when displaying currency or percentages — long decimals clutter reports and confuse stakeholders.
✏️ Practice Exercises
1. Count the total number of orders in the orders table.
2. Calculate the total value of all Electronics products (sum of price × quantity for Electronics).
3. Find the average price of products in each category, rounded to 2 decimal places.
Show Answers
-- 1.
SELECT COUNT(*) AS total_orders FROM orders;

-- 2.
SELECT SUM(price) AS total_electronics_value
FROM products
WHERE category = 'Electronics';

-- 3.
SELECT
  category,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY category;