Querying Data›Extra· 35 min read

LIKE, IN & Aggregate Functions

Search text, match lists, and summarise data with COUNT, SUM and GROUP BY.

What you will learn

Search with LIKE
Match a list with IN
Summarise with aggregate functions

Search and match

Sometimes = is too strict. LIKE does pattern matching for text (find names that start with A), IN checks if a value is in a list of choices, and BETWEEN checks a range of numbers or dates. All three go inside a WHERE clause.

Note: A few examples here use the city column we added in the WHERE lesson (with ALTER TABLE users ADD city VARCHAR(100); and a couple of UPDATE lines). If you skipped that, add the column the same way before trying the city examples.

LIKE, IN and BETWEEN

SELECT * FROM users WHERE name LIKE 'A%';      -- starts with A
SELECT * FROM users WHERE name LIKE '%a%';     -- contains a
SELECT * FROM users WHERE city IN ('Mumbai', 'Delhi');
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

Reading each line: LIKE 'A%' matches any name that starts with A (the % stands for “any characters after”). LIKE '%a%' matches any name that contains an a anywhere. IN ('Mumbai', 'Delhi') is a shorter way of writing city = 'Mumbai' OR city = 'Delhi'. BETWEEN 100 AND 500 keeps prices from 100 to 500 inclusive (both ends count).

Note: Output (for name LIKE 'A%' with our users): Asha asha@x.com Only Asha’s name begins with the letter A, so only her row matches. Ravi and Meera are filtered out.

Summarise with aggregates

So far each query returned individual rows. Aggregate functions instead boil many rows down to one answer — a count, a total, an average. COUNT(*) counts rows, AVG() averages a column, and GROUP BY runs the aggregate once per group (e.g. once per city) instead of over the whole table.

COUNT, AVG and GROUP BY

SELECT COUNT(*) FROM users;              -- how many users
SELECT AVG(age) FROM users;              -- average age
SELECT city, COUNT(*) FROM users GROUP BY city;   -- users per city

The first query returns a single number — how many rows are in users. The second returns the average of the age column. The third is the powerful one: GROUP BY city gathers rows that share the same city into groups, then COUNT(*) counts each group separately, so you get one row per city with its user count.

Note: Output (for the GROUP BY query): city COUNT(*) Bengaluru 1 Mumbai 2 MySQL made one group per distinct city and counted the rows in each. Mumbai has 2 users, Bengaluru has 1. COUNT(*) on its own (first query) would instead return a single number like 3 — the total across all rows.

Tip: % is a wildcard in LIKE: 'A%' = starts with A, '%a' = ends with a, '%a%' = contains a. GROUP BY collapses rows into groups for per-category totals.

Q. Which finds names starting with "S"?

Answer: LIKE 'S%' matches any name beginning with S (% is a wildcard for any characters).

✍️ Practice

Find all users whose name contains “a”.
Count how many products are in each category with GROUP BY.

🏠 Homework

Write a query for the average price of products per category.