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.
| Function | Returns |
|---|---|
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
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, 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
- Write one query on the
productstable that returns a single row with three columns: total— how many products there are,cheapest— the lowest price,average_price— the average price, rounded to 2 decimals.
- The result is a single row with three columns
- The columns are named
total,cheapestandaverage_price - The three values are correct
Lesson complete!
Next Lesson: Grouping