aggregation basics (COUNT, SUM, AVG, MIN, MAX)

Up till now every query gave back rows from the table. Aggregates squash a bunch of rows into one number. "How many orders?", "total revenue?", "average salary?", all of these are aggregates.

the five you'll use 99% of the time

SELECT
  COUNT(*)    AS total_orders,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order,
  MIN(amount) AS smallest,
  MAX(amount) AS biggest
FROM orders;

One row comes back, 10 orders squashed into a summary.

COUNT has three versions

SELECT COUNT(*)              FROM customers;  -- 8, every row
SELECT COUNT(city)           FROM customers;  -- 7, skips the NULL city
SELECT COUNT(DISTINCT city)  FROM customers;  -- 4, Delhi Mumbai Bangalore Chennai

Know the difference, interviewers love this.

with WHERE

WHERE filters first, then the aggregate runs on what's left.

SELECT SUM(amount) AS delivered_revenue
FROM orders
WHERE status = 'delivered';

GROUP BY (one number per group)

Ok this is the big one. Instead of one number for the whole table, you want one number per something, per city, per category, per month.

SELECT category, COUNT(*) AS products, AVG(price) AS avg_price
FROM products
GROUP BY category;

category	products	avg_price
Electronics	4	3899.00
Stationery	2	135.00
Home	2	874.50

Think of it like sql making little piles, one pile per category, and then running COUNT and AVG on each pile.

the rule that'll save you errors

Every column in SELECT has to either be in GROUP BY or be inside an aggregate.

-- errors
SELECT category, product_name, COUNT(*)
FROM products
GROUP BY category;

Why? The Electronics pile has 4 products, which product_name should it show? Sql doesn't know, so it refuses. (mysql sometimes lets it slide and just picks one randomly, which is worse honestly.)

grouping by more than one column

SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id;

Now a pile is every unique (customer, status) combo.

a little preview of HAVING

You can't filter on an aggregate in WHERE, because WHERE runs before grouping. HAVING is the WHERE for groups:

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

Full topic on this in intermediate.

rounding

AVG often gives ugly decimals like 2308.2000000000000000. Wrap it:

SELECT ROUND(AVG(amount), 2) FROM orders;

stuff that trips people up

• selecting a column that's not in GROUP BY and not aggregated.
• using WHERE for aggregate conditions instead of HAVING.
• SUM on an empty result gives NULL not 0, use COALESCE(SUM(x), 0).
• integer division in AVG on some databases, postgres handles it fine but sqlite/sql server might not.

practice

Check queries.sql.

-- aggregation basics
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue, AVG(amount) AS avg_order,
       MIN(amount) AS smallest, MAX(amount) AS biggest
FROM orders;

SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM customers;

SELECT SUM(amount) AS delivered_revenue FROM orders WHERE status = 'delivered';

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;


-- practice ---------------------------------------------------------

-- 1. how many products are there?
-- 2. what's the highest salary in the company?
-- 3. total amount of all orders that weren't cancelled
-- 4. number of customers in each city (include the NULL city group too, see what happens)
-- 5. average salary per department, rounded to 0 decimals, highest first
-- 6. number of orders per status
-- 7. for each customer_id: how many orders and how much they spent in total


-- answers ----------------------------------------------------------

-- 1
SELECT COUNT(*) FROM products;

-- 2
SELECT MAX(salary) FROM employees;

-- 3
SELECT SUM(amount) FROM orders WHERE status <> 'cancelled';

-- 4
SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
-- NULL shows up as its own group, GROUP BY treats all NULLs as one pile

-- 5
SELECT department, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- 6
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 7
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;