Lesson 5 of 10
Aggregate functions
- 11 min
- 6 checks
- 3 hints
- Go to task
Goal
COUNT, SUM and AVG
Explanation
Aggregate functions turn many rows into a single value: COUNT counts rows, SUM adds values up, AVG works out the average, and MIN and MAX give you the smallest and largest values. The database computes them right where the data lives, so you don't have to pull a thousand rows into your program just to add them up there.
There's one difference a lot of people forget: COUNT(*) counts every row, while COUNT(rating) counts only the values that aren't NULL. So an order that was never rated is counted by the first and skipped by the second.
The result column is named after the expression itself, like SUM(amount). If you want a clear name that's easy for code to read later, use AS: SUM(amount) AS revenue.
Your task
From the orders table, show a single row with orders_count for the number of orders, rated_count for the number of rated orders, revenue for the total of amount, avg_amount for its average, and min_amount and max_amount for the smallest and largest amount.
Checks
6 checks- The columns have the required names
- The orders_count column counts every order
- The rated_count column skips orders with no rating
- The revenue column is the total of the amounts
- The avg_amount column is the average, decimals included
- The min_amount and max_amount columns hold the smallest and largest amount
Tables in this lesson
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
city TEXT NOT NULL,
amount INTEGER NOT NULL,
rating INTEGER
);
INSERT INTO orders (customer, city, amount, rating) VALUES
('Layan', 'Riyadh', 120, 5),
('Khalid', 'Jeddah', 300, 4),
('Layan', 'Riyadh', 80, NULL),
('Reem', 'Dammam', 45, 3),
('Sara', 'Riyadh', 210, 5),
('Khalid', 'Jeddah', 50, NULL),
('Fahad', 'Jeddah', 150, 2),
('Noura', 'Riyadh', 95, 4),
('Omar', 'Mecca', 400, NULL),
('Layan', 'Riyadh', 35, 4),
('Reem', 'Dammam', 130, NULL),
('Sara', 'Riyadh', 70, 3);