SQL Basics · Lesson 5 of 9

Counting and Calculating

So far each row of the result was a row of the table. Aggregate functions work differently: they take many rows and return a single value.

FunctionReturns
COUNT(*)the number of rows
SUM(column)the total
AVG(column)the average
MIN(column)the smallest value
MAX(column)the largest value
SELECT COUNT(*) FROM products;

WHERE is applied first, so you can summarize part of a table:

SELECT AVG(price)
FROM products
WHERE category = 'Home';

Naming a column with AS

A calculated column gets an awkward name such as AVG(price). AS gives it a better one, called an alias:

SELECT COUNT(*) AS products,
       MAX(price) AS highest_price
FROM products;

Calculating per row

You can also calculate with columns in an ordinary query. This gives the value of the stock for every product:

SELECT name, price * stock AS stock_value
FROM products;

ROUND(value, 2) rounds to two decimals, which is useful with averages.

Tables in the practice database
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, customer_id, product_id, quantity, ordered

Every run starts from a fresh copy of the database, so nothing you do can break it.

Your Task

  1. Write one query on the products table that returns a single row with three columns:
  2. total — how many products there are,
  3. cheapest — the lowest price,
  4. average_price — the average price, rounded to 2 decimals.
  • The result is a single row with three columns
  • The columns are named total, cheapest and average_price
  • The three values are correct