SQL Querying and Aggregation: Filtering, Grouping, and Summarising Data

Once you can pull columns out of a table, the next step is asking sharper questions: how many rows match a condition, what’s the average within each group, which categories exceed a threshold. This is where SQL becomes a genuine analytical tool. The whole of it rests on a small set of clauses that always appear in the same fixed order, and understanding that order is the key to understanding everything else.

The Order of Clauses

A SQL query is a recipe with a fixed ingredient order, like a sandwich assembly line. You can skip steps, but you cannot reorder them. The full sequence looks like this:

SELECT    col1, AGG(col2) AS alias     -- what to return
FROM      table_name                   -- where the data lives
WHERE     condition                    -- filter ROWS (before grouping)
GROUP BY  col1                         -- group rows together
HAVING    AGG(col2) > value            -- filter GROUPS (after grouping)
ORDER BY  col1 DESC                    -- sort the results
LIMIT     10;                          -- cap the number of rows

The pipeline runs roughly like this: pull rows from the table, throw out rows that fail WHERE, bundle the survivors into groups with GROUP BY, throw out whole groups that fail HAVING, sort what’s left, then take the top few. Here is the curious part: even though you write SELECT first, the database actually does it almost last, because it has to know which rows survive before it knows what to return. Commit the order SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT to memory, because every query you write from now on follows it.

Counting with COUNT()

The simplest aggregate is COUNT, and it has three forms that mean subtly different things.

SELECT COUNT(*) AS count_records
FROM people;

COUNT(*) answers “how many rows are in this table, total?” The asterisk means any row, regardless of what it contains, so even rows full of NULLs get counted, because a row exists. Think of it as counting people in a room without caring whether they brought ID.

When you put a column name inside the brackets, the rules change:

SELECT COUNT(birthdate) AS count_birthdate
FROM people;

Now SQL counts only rows where that column actually has a value, skipping any with NULL. This answers “how many people have a known birthdate?” and it is a quick way to spot missing data. If COUNT(*) returns 1000 but COUNT(birthdate)returns 850, you instantly know 150 people are missing a birthdate.

Add DISTINCT and you get the number of different values:

SELECT COUNT(DISTINCT country) AS count_distinct_countries
FROM films;

If 500 films were made in one of just four countries, this returns 4, not 500. It is the SQL way of asking how much variety, or cardinality, a column has, which is useful to know before doing anything else with it.

Filtering Rows with WHERE

WHERE is the bouncer at the door. SQL walks through every row, hands it to the bouncer, and only rows where the condition is true are kept. Everything else is discarded before any further processing happens.

SELECT film_id, imdb_score
FROM reviews
WHERE imdb_score > 7.0;

All the standard comparison operators work: >, <, >=, <=, =, and <> (or !=) for “not equal.” You can combine WHERE with LIMIT to find matching rows but only see a handful, which is handy while exploring:

SELECT film_id, facebook_likes
FROM reviews
WHERE facebook_likes < 1000
LIMIT 10;

Filtering text comes with two rules that trip up newcomers:

SELECT COUNT(language) AS count_spanish
FROM films
WHERE language = 'Spanish';

First, strings go in single quotes, not double quotes. Double quotes mean something different in SQL, referring to identifiers like column names. Second, use a single = for equality, not the == you might know from Python or JavaScript. As for case sensitivity, it depends on the database: PostgreSQL treats 'Spanish' and 'spanish' as different, while MySQL by default does not. The safest assumption is that string comparisons are case-sensitive until you confirm otherwise.

Combining Conditions with AND, OR, and BETWEEN

AND chains conditions together with the rule that every one must be true for a row to survive:

SELECT *
FROM films
WHERE release_year > 2000
  AND release_year < 2010
  AND language = 'German';

Think of it as a series of bouncers, each checking one thing; fail any single check and you do not get in. Note that strict >and < exclude 2000 and 2010 themselves. To include them you would use >= and <=, or reach for BETWEEN.

OR is the opposite: at least one condition must be true. But mixing AND and OR introduces a trap:

SELECT title, release_year
FROM films
WHERE (release_year = 1990 OR release_year = 1999)
  AND (language = 'English' OR language = 'Spanish')
  AND gross > 2000000;

SQL evaluates AND before OR, so without parentheses, A OR B AND C actually means A OR (B AND C), which is probably not what you intended. That is why the parentheses around the year and language groups are essential here; they tell SQL to evaluate each OR group as a unit before combining it with the rest. The rule of thumb is simple: any time you mix ANDand OR, use parentheses. They cost nothing and prevent real bugs.

BETWEEN is a readability shortcut for an inclusive range:

SELECT title, release_year
FROM films
WHERE release_year BETWEEN 1990 AND 2000
  AND budget > 100000000
  AND (language = 'Spanish' OR language = 'French');

BETWEEN 1990 AND 2000 is exactly the same as release_year >= 1990 AND release_year <= 2000. The database does identical work; it just reads more cleanly. The one thing to remember is that it is inclusive on both ends, which beginners often forget. It also works on dates and text, not just numbers.

Pattern Matching and List Membership: LIKE and IN

LIKE handles fuzzy text matching using two wildcards. The % symbol means any number of any characters, including zero, and _ means exactly one character.

SELECT name
FROM people
WHERE name NOT LIKE 'A%';

So 'A%' matches anything starting with A, and adding NOT flips it to keep everything that does not match. The patterns are straightforward once you see them: '%a' ends with a, '%son%' contains “son” anywhere, and '_at' matches any single character followed by “at,” catching bat, cat, and hat. LIKE is weaker than full regular expressions, with no character classes, but it is universal across SQL databases and good enough for most “starts with,” “contains,” and “ends with” needs.

IN checks whether a value appears in a list, replacing a string of OR conditions:

SELECT title, certification, language
FROM films
WHERE certification IN ('NC-17', 'R')
  AND language IN ('English', 'Italian', 'Greek');

Writing this out as language = 'English' OR language = 'Italian' OR language = 'Greek' would be verbose and error-prone. IN says the same thing in one clean line, and there is also NOT IN for “none of these.” One subtle gotcha: NOT IN behaves strangely when the list contains a NULL, sometimes returning zero rows unexpectedly, so if your list might include NULLs, handle them separately.

Handling Missing Values with NULL

NULL is SQL’s way of saying “unknown” or “no value here,” and it has a genuinely weird property: it is not equal to anything, not even to itself.

SELECT COUNT(*) AS count_language_known
FROM films
WHERE language IS NOT NULL;

This is the single biggest pitfall for beginners. Writing WHERE language = NULL returns zero rows, even when there are NULLs in the column. The reason is that NULL = NULL does not evaluate to true; it evaluates to NULL, meaning “I don’t know whether these two unknowns are equal.” To check for missing values, SQL gives you the special operators IS NULLand IS NOT NULL. Always use those, and never use = NULL.

Aggregate Functions

Aggregate functions take a whole column and crush it into a single number. MIN returns the smallest value, MAX the largest, AVG the mean, SUM the total, and COUNT the count.

SELECT MIN(duration) AS shortest_film
FROM films;

Without GROUP BY, these collapse the entire table into one row. Notice the result has no film title attached, just a single number, because the column has been crushed down. If you wanted to know which film has that minimum duration, that is a different query, using either a subquery or ORDER BY duration LIMIT 1.

Aggregates respect any WHERE filter, which runs first:

SELECT MAX(gross) AS highest_gross
FROM films
WHERE release_year BETWEEN 2000 AND 2012;

SQL filters down to films from this period, then finds the maximum among the survivors, so the answer is the highest gross within that era, not overall.

You can also do arithmetic with aggregates:

SELECT (MAX(release_year) - MIN(release_year)) / 10.0 AS number_of_decades
FROM films;

This subtracts the earliest year from the latest to get a span, then divides by ten to convert it into decades. That 10.0, with its decimal point, is doing something crucial, and it leads to one of the most common traps in all of SQL:

SELECT title, duration / 60.0 AS duration_hours
FROM films;

duration / 60 and duration / 60.0 look identical but produce different results in many SQL dialects. When both operands are integers, SQL performs integer division and chops off the decimal, so a 90-minute film reports as 1 hour rather than 1.5. Adding .0 to the divisor forces decimal arithmetic and gives you the correct 1.5. Always use 60.0, or cast explicitly, when you want a fractional answer. This one catches everyone at some point.

Rounding with ROUND()

Aggregates often produce ugly numbers like 1234.56789012, which nobody wants in a report. ROUND(x, n) trims a value to ndecimal places:

SELECT ROUND(AVG(facebook_likes), 1) AS avg_facebook_likes
FROM reviews;

The second argument controls precision, and it has a clever feature most people miss. Positive numbers round to that many digits after the decimal, zero rounds to a whole number, and negative numbers round to that many digits before the decimal:

SELECT ROUND(AVG(budget), -3) AS avg_budget_thousands
FROM films;

ROUND(x, -3) rounds to the nearest thousand, so 1,234,567 becomes 1,235,000. Use -2 for the nearest hundred or -6 for the nearest million. This is ideal for executive summaries where you want to say “average budget around 58 million” rather than “58,234,567.42.” You can also combine rounding with the decimal-division trick to get clean fractional hours:

SELECT title, ROUND(duration / 60.0, 2) AS duration_hours
FROM films;

Sorting with ORDER BY

ORDER BY sorts your results. DESC means descending, biggest first; ASC means ascending, smallest first; and ascending is the default if you omit the keyword.

SELECT title, duration
FROM films
ORDER BY duration DESC;

For text, ascending is alphabetical, and for dates it runs oldest to newest. Sorting happens late in the pipeline, after WHERE, GROUP BY, and HAVING, which is exactly why LIMIT 10 without an ORDER BY just returns whatever ten rows the database grabbed first. Pair it with ORDER BY duration DESC LIMIT 10 and you get the genuine ten longest films.

You can sort by several columns, where later columns break ties in earlier ones:

SELECT release_year, duration, title
FROM films
ORDER BY release_year, duration;

This lists all films from the earliest year sorted by duration, then the next year, and so on. You can even mix directions per column, like ORDER BY release_year DESC, duration ASC for newest year first but shortest film first within each year.

Grouping with GROUP BY

GROUP BY is the moment SQL stops being a spreadsheet filter and starts doing real analytics.

SELECT release_year, AVG(duration) AS avg_duration
FROM films
GROUP BY release_year;

Imagine dumping all your rows into buckets, one bucket per unique value of release_year, then running the aggregate within each bucket and getting back one row per bucket. If your films span 50 years, you get 50 rows out, each showing that year’s average duration. The aggregate happens within each group, not across the whole table.

Grouping by several columns creates one bucket per combination:

SELECT release_year, country, MAX(budget) AS max_budget
FROM films
GROUP BY release_year, country
ORDER BY release_year, country;

Now you get a row for “1995, USA,” another for “1995, France,” another for “1996, USA,” and so on, with the maximum budget computed within each year-and-country pair. This is how you build cross-tabulated summaries, the SQL equivalent of an Excel pivot table.

There is one rule with GROUP BY that you must internalise: every column in SELECT that is not inside an aggregate function must appear in GROUP BY. This is not arbitrary; it is logical. Each output row represents one bucket, so every column you select has to either describe the bucket itself, meaning it is one of the grouping columns, or be a single number computed from the bucket’s contents, meaning an aggregate. What you cannot do is select a column that takes many different values within a bucket without aggregating it, because SQL would not know which value to show. This query fails for exactly that reason:

-- WRONG — release_year is not aggregated and not in GROUP BY
SELECT release_year, MAX(budget)
FROM films
GROUP BY country;

The buckets here are shaped by country, but the query asks to display release_year, which has many values within each country bucket. Earliest? Latest? SQL refuses to guess and throws an error. The fix is to either add release_year to GROUP BY, making finer buckets, or aggregate it with something like MAX(release_year).

Filtering Groups with HAVING

WHERE and HAVING both filter, but they act at different stages of the pipeline, and this distinction is one of the most important in SQL.

SELECT country, COUNT(DISTINCT certification) AS certification_count
FROM films
GROUP BY country
HAVING COUNT(DISTINCT certification) > 10;

WHERE runs before grouping and filters individual rows. HAVING runs after grouping and filters whole groups based on aggregate results. You cannot put COUNT(...) > 10 in WHERE, because at the moment WHERE runs the groups do not exist yet and the count has not been computed. By the time HAVING runs, each group has its aggregates ready, so it can ask questions about them. The mental shorthand is this: WHERE is for raw values, HAVING is for aggregates.

A complete analytical query brings it together:

SELECT country, AVG(budget) AS average_budget
FROM films
GROUP BY country
HAVING AVG(budget) > 1000000000
ORDER BY average_budget DESC;

This buckets by country, computes each country’s average budget, discards those averaging under a billion, and sorts the survivors highest first. One portability note: you can usually reference the alias average_budget in ORDER BY, but generally not in HAVING, which is why we repeat AVG(budget) rather than writing HAVING average_budget > 1000000000. PostgreSQL allows the alias; many databases do not, so repeating the expression is the safe, portable choice.

Everything Together

Here is the entire pipeline in a single query:

SELECT release_year,
       AVG(budget)  AS avg_budget,
       AVG(gross)   AS avg_gross
FROM films
WHERE release_year > 1990          -- filter rows first
GROUP BY release_year              -- then group
HAVING AVG(budget) > 60000000      -- filter groups
ORDER BY avg_gross DESC            -- sort results
LIMIT 1;                           -- take only the top row

Trace it step by step in the order the database executes it. Start with all films. Drop those from 1990 or earlier with WHERE. Bucket the survivors by year with GROUP BY. Drop years whose average budget did not exceed 60 million with HAVING. For each surviving year, compute the two averages in SELECT. Sort the years by average gross, highest first, with ORDER BY. Finally, take just the top one with LIMIT. The answer to a real analytical question, “of recent years with big-budget films, which had the highest average gross?”, in seven lines. That is what SQL is for.

WHERE Versus HAVING: The Decision Rule

When you need to filter, the choice between WHERE and HAVING comes down to a single question. If you are filtering on a raw column value, like a title, country, or release year, use WHERE. If you are filtering on an aggregated value, like an average, sum, or count, use HAVING. That is the whole rule, and it follows directly from the fact that WHERE runs before aggregation exists and HAVING runs after.

Quick Reference

-- Count
SELECT COUNT(*) FROM table;
SELECT COUNT(col) FROM table;            -- excludes NULLs
SELECT COUNT(DISTINCT col) FROM table;   -- unique non-NULLs
-- Filter rows
WHERE col > 100
WHERE col = 'Spanish'
WHERE col BETWEEN 1990 AND 2000          -- inclusive
WHERE col LIKE 'A%'                      -- starts with A
WHERE col NOT LIKE '%a'                  -- does not end with a
WHERE col IN ('G', 'PG', 'PG-13')
WHERE col IS NULL
WHERE col IS NOT NULL
-- Combine conditions
WHERE a = 1 AND b = 2
WHERE (a = 1 OR a = 2) AND c = 3         -- parentheses for clarity
-- Aggregates
SELECT MIN(col), MAX(col), AVG(col), SUM(col) FROM table;
SELECT ROUND(AVG(col), 2) AS rounded_avg FROM table;
SELECT (MAX(col) - MIN(col)) / 10.0 AS calculated FROM table;
-- Group and sort
GROUP BY col1, col2
HAVING AVG(col) > 1000
ORDER BY col DESC
LIMIT 10;

Conclusion

SQL querying is built on a fixed pipeline, and almost everything follows from understanding it. WHERE filters raw rows before anything else happens. Aggregate functions like COUNT, SUM, AVG, MIN, and MAX crush columns into single numbers. GROUP BY splits the data into buckets so those aggregates run per group rather than over the whole table, with the rule that every non-aggregated column you select must appear in the grouping. HAVING then filters those groups on their aggregate results, which is precisely why it exists alongside WHERE. Wrap it up with ORDER BY to sort and LIMIT to cap the output. Watch out for the recurring traps along the way: integer division needing a .0, NULL never equalling itself, and parentheses being essential when mixing AND and OR. Master this set of clauses and the order they run in, and you can answer genuine analytical questions directly from the database.

See you soon.