Filtering, Sorting, and Aggregating

Lesson 6 of 8 · Database Fundamentals · Beginner

Once you can write a basic SELECT query, the next step is learning to filter rows precisely, sort by multiple columns, and summarize data using aggregate functions. These are the tools that turn raw database tables into useful answers.

Comparison Conditions

The WHERE clause supports a range of comparison operators:

• = equal to
• <> or != not equal to
• > greater than, < less than
• >= greater than or equal to, <= less than or equal to

You can combine conditions with AND and OR:

SELECT program_name, capacity, program_type
FROM programs
WHERE capacity >= 10
  AND program_type = 'Workshop'
ORDER BY capacity;

Filtering Null Values

You cannot use = NULL to find null values. Use IS NULL or IS NOT NULL:

SELECT program_name
FROM programs
WHERE start_date IS NULL;

This returns programs where the start date has not been entered yet.

Sorting by Multiple Columns

ORDER BY can sort by more than one column. The database sorts by the first column, then by the second within ties:

SELECT program_name, program_type, start_date
FROM programs
ORDER BY program_type ASC, start_date DESC;

Aggregate Functions

Aggregate functions summarize a set of rows into a single value:

• COUNT(*) — counts the number of rows
• SUM(column) — adds up numeric values
• AVG(column) — calculates the average
• MIN(column) — finds the smallest value
• MAX(column) — finds the largest value

GROUP BY

GROUP BY divides rows into groups and applies the aggregate function to each group separately:

SELECT location_id, COUNT(*) AS program_count
FROM programs
GROUP BY location_id;

Each output row represents one location and shows how many programs are held there. The AS program_count gives the calculated column a readable name.

SELECT program_type, AVG(capacity) AS average_capacity
FROM programs
GROUP BY program_type
ORDER BY average_capacity DESC;

This shows the average capacity for each program type, sorted from largest to smallest average.

Interpreting Grouped Results

When you use GROUP BY, each output row represents a group — not an individual record. The level of detail changes. A count of 12 for a location means 12 programs are associated with that location, not that there are 12 rows in the original table.

Always validate grouped results by checking whether the totals make sense. If you expect 50 programs total and your grouped counts add up to 47, three programs may have a null location_id and were excluded from the grouping.

Key Takeaways

• WHERE supports comparison operators and AND/OR combinations.
• Use IS NULL and IS NOT NULL to filter null values — not = NULL.
• ORDER BY can sort by multiple columns in different directions.
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarize groups of rows.
• GROUP BY changes the level of detail — validate totals to confirm correctness.