0%
DQDigital Qasas Courses/SQL for Professionals
Sign inAll courses
  • What SQL is, and why professionals learn it
  • Learn faster: pair this course with a free AI assistant
  • SELECT and FROM: your first real query
  • WHERE: keep only the rows you want
  • Combine conditions: AND, OR, IN, BETWEEN, LIKE, NULL
  • ORDER BY, LIMIT, and DISTINCT
  • COUNT, SUM, AVG, MIN, MAX
  • GROUP BY: one number per group
  • HAVING: filter the groups
  • Why tables relate: keys and joins
  • INNER JOIN: matching rows from two tables
  • LEFT JOIN: keep everything, find what is missing
  • Subqueries: a query inside a query
  • CASE: if-and-then inside a query
  • Window functions: a first look
  • Putting it together, and a cheat sheet
  • Practice challenges
Aggregate: turn rows into numbers

COUNT, SUM, AVG, MIN, MAX

Last updated 14 June 2026

So far each query returned rows from a table. Aggregate functions collapse many rows into a single number. They are how you get totals, averages, and counts.

  • COUNT(*):how many rows
  • SUM(column):adds the values
  • AVG(column):the average
  • MIN(column) / MAX(column):smallest and largest

How many orders are there, in total?

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT COUNT(*) AS total_orders
FROM orders;

You can ask for several aggregates at once. Here are the average, cheapest, and dearest product prices in one row.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT AVG(price) AS avg_price, MIN(price) AS cheapest, MAX(price) AS dearest
FROM products;

Your turn

Find the total number of products and the sum of all their prices. Name the columns n_products and total_price.

Count the products and sum their prices.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT 
FROM products;
SELECT COUNT(*) AS n_products, SUM(price) AS total_price FROM products;
COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. The two can disagree, and that gap is often the thing worth reporting.

Which function gives you the average value of a numeric column?

  • AVG(column)
  • SUM(column)
  • COUNT(column)
AVG returns the mean. SUM adds the values and COUNT counts the rows, so you would need SUM / COUNT to get the average the long way.
← PreviousORDER BY, LIMIT, and DISTINCTNext →GROUP BY: one number per group