Chapter 6: Filtering, Sorting, and Aggregation

Now that you know the basic CRUD operations, let's master the tools for asking precise questions of your data.

WHERE — Filtering Rows

Comparison Operators

SELECT * FROM users WHERE age = 30;       -- equals
SELECT * FROM users WHERE age != 30;      -- not equals (also: <>)
SELECT * FROM users WHERE age > 25;       -- greater than
SELECT * FROM users WHERE age >= 25;      -- greater or equal
SELECT * FROM users WHERE age BETWEEN 20 AND 30;  -- inclusive range

Logical Operators

-- AND: both conditions must be true
SELECT * FROM users WHERE age > 25 AND name LIKE 'A%';

-- OR: either condition
SELECT * FROM users WHERE age < 20 OR age > 60;

-- NOT: negate
SELECT * FROM users WHERE NOT (age BETWEEN 20 AND 30);

-- IN: match any value in a list
SELECT * FROM users WHERE country IN ('Sweden', 'Norway', 'Finland');

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE phone IS NOT NULL;

Pattern Matching

-- LIKE: simple patterns (% = any chars, _ = one char)
SELECT * FROM users WHERE name LIKE 'A%';      -- starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with
SELECT * FROM users WHERE name LIKE '_ob';     -- 3 chars ending in "ob"

-- ILIKE: case-insensitive (PostgreSQL)
SELECT * FROM users WHERE name ILIKE 'alice';  -- matches Alice, ALICE, etc.

ORDER BY — Sorting Results

-- Sort ascending (default)
SELECT * FROM users ORDER BY name;

-- Sort descending
SELECT * FROM users ORDER BY age DESC;

-- Multiple sort keys
SELECT * FROM users ORDER BY country ASC, age DESC;

-- NULLs: control where they appear
SELECT * FROM users ORDER BY age NULLS LAST;

LIMIT and OFFSET — Pagination

-- First 10 results
SELECT * FROM users ORDER BY id LIMIT 10;

-- Page 2 (skip first 10, get next 10)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

Aggregate Functions

Aggregate functions compute a single value from multiple rows:

SELECT COUNT(*) FROM users;                    -- total rows
SELECT COUNT(phone) FROM users;               -- non-NULL phones
SELECT SUM(salary) FROM employees;            -- total salary
SELECT AVG(age) FROM users;                   -- average age
SELECT MIN(created_at) FROM users;            -- earliest signup
SELECT MAX(salary) FROM employees;            -- highest salary
SELECT COUNT(DISTINCT country) FROM users;   -- unique countries

GROUP BY — Aggregating by Category

GROUP BY splits rows into groups and applies aggregate functions per group:

-- Count users per country
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- Result:
-- country  | user_count
-- Sweden   | 42
-- Norway   | 15
-- Finland  | 23

-- Average salary per department
SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id;

-- Multiple grouping columns
SELECT country, city, COUNT(*) AS user_count
FROM users
GROUP BY country, city
ORDER BY user_count DESC;

HAVING — Filtering Groups

WHERE filters rows before grouping. HAVING filters groups after aggregation:

-- Countries with more than 20 users
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 20;

-- Departments where average salary exceeds 80K
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE active = true          -- WHERE: filter rows first
GROUP BY dept_id
HAVING AVG(salary) > 80000  -- HAVING: filter groups after
ORDER BY avg_sal DESC;

DISTINCT — Removing Duplicates

-- Unique countries
SELECT DISTINCT country FROM users;

-- Unique combinations
SELECT DISTINCT country, city FROM users;

CASE — Conditional Logic

SELECT name, salary,
  CASE
    WHEN salary > 100000 THEN 'Senior'
    WHEN salary > 70000  THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

Putting It All Together

-- "Show me the top 5 countries by active user count,
--  but only countries with at least 10 users,
--  excluding test accounts"

SELECT
    country,
    COUNT(*) AS active_users,
    AVG(age) AS avg_age
FROM users
WHERE active = true
  AND email NOT LIKE '%@test.com'
GROUP BY country
HAVING COUNT(*) >= 10
ORDER BY active_users DESC
LIMIT 5;

Mark as Complete