MySQL online course

Advanced SELECT Statements in MySQL

Learn how MySQL COUNT(), DISTINCT, and WHERE clauses count rows, remove repeated result values, and filter records without changing stored data.

A SELECT statement retrieves data from one or more tables. Its result set is the rows and columns returned by the query. By adding functions and clauses, you can change the result set to answer questions such as: How many rows match? Which values are unique? Which records satisfy a condition?

This lesson covers three common techniques: COUNT() for summaries, DISTINCT for removing repeated values from query output, and WHERE for filtering rows. These operations only affect the result set. They do not insert, update, or delete data stored in the table.

Sample data for the examples

The examples use a table named testtb with columns named name, surname, and year. The table contains two rows whose name value is John.

First inspect the complete starting dataset:

SELECT * FROM testtb;

Checking a base query first makes it easier to verify later filters, counts, and distinct results.

Counting rows with COUNT()

COUNT() is an aggregate function. An aggregate function calculates a summary value from multiple rows. COUNT(*) counts every row matched by the query, including rows that contain NULL in individual columns.

Count every row

SELECT COUNT(*) FROM testtb;

With the sample data, the result contains the value 4. Unlike a normal query that returns one result row for each table row, this query returns one summary result row containing the total.

Count only matching rows

Place a WHERE clause before the count is calculated to count only rows satisfying a condition:

SELECT COUNT(*)
FROM testtb
WHERE year > 1980;

This returns 2 for the sample data because the rows for Mary and John Brown have years greater than 1980. The filtering happens first, and COUNT(*) summarizes the remaining rows.

COUNT(*) and COUNT(column_name)

COUNT(*) counts every matching row. In contrast, COUNT(column_name) counts only matching rows where that particular column is not NULL.

NULL is a marker for an unknown or missing value. It is not the same as zero or an empty string.

SELECT COUNT(*) AS all_rows,
       COUNT(surname) AS rows_with_surname
FROM testtb;

If one matching row has a NULL surname, all_rows still includes it, while rows_with_surname does not. Use COUNT(*) when the goal is to count every matching row.

Removing repeated result values with DISTINCT

A regular SELECT preserves rows. Therefore, if several rows have the same value, that value can appear several times in the result:

SELECT name FROM testtb;

The result values are John, Mary, John, and Ana. The repeated John reflects two different table rows.

Add DISTINCT after SELECT to return each selected value once:

SELECT DISTINCT name FROM testtb;

The result contains John, Mary, and Ana. DISTINCT changes only the displayed query output. It does not delete or merge the two John records in testtb.

DISTINCT with multiple columns

When more than one column is selected, DISTINCT evaluates the complete combination of selected values:

SELECT DISTINCT name, year
FROM testtb;

Here, uniqueness is based on the pair (name, year), not on name alone. A name can therefore appear more than once if its associated year differs. To get unique first names, select only name.

Filtering rows with WHERE

WHERE restricts a query to rows for which a condition evaluates as true. A condition compares a column value with another value using a comparison operator.

Basic syntax

SELECT column_list
FROM table_name
WHERE condition;

You can select every column or only chosen columns while using WHERE:

SELECT *
FROM testtb
WHERE name = 'John';

SELECT name, surname
FROM testtb
WHERE year > 1980;

The first query returns both rows whose name is John. The second query returns only the selected columns from rows whose year is greater than 1980.

Comparison operators

In a condition such as year > 1980, year is the column, > is the operator, and 1980 is the comparison value. MySQL evaluates this condition for each candidate row.

String and numeric literal values

A string literal is a text value written directly in SQL. Text values in MySQL conditions are ordinarily enclosed in single quotes:

SELECT * FROM testtb WHERE name = 'John';

Numeric literals are ordinarily written without quotes:

SELECT * FROM testtb WHERE year > 1980;

Missing quotes around text can cause a syntax error or an unintended comparison. Conversely, treating a number as text can make the statement less clear and may lead to type-conversion behavior. Match the literal style to the column's data type.

Reading and validating result grids

A SQL client may display these queries in visibly different ways:

Do not confuse the number displayed by COUNT(*) with a client's separate result-grid row count. A count query returns one result row, whose numeric value might be 4. The client may report that the result set contains one returned row because there is one summary row. Read the numeric value in the count column as the table or filtered-row total.

Practical query sequence

1. Inspect the source rows with SELECT * FROM testtb;.
2. Select a single column with SELECT name FROM testtb; to see whether repeated values exist.
3. Use SELECT DISTINCT name FROM testtb; when the output should contain unique names.
4. Apply a text condition with WHERE name = 'John'.
5. Apply a numeric condition with WHERE year > 1980.
6. Use COUNT(*) with the condition when you need the number of matching rows.

Troubleshooting common mistakes

A name appears multiple times

This usually means multiple table rows contain that name. A regular SELECT returns each matching row. Use DISTINCT only if you need unique displayed values; it does not remove records.

A text filter fails or returns unexpected results

Check that the text value is enclosed in single quotes, as in name = 'John'. Also verify the column name and the spelling and capitalization of the stored value.

COUNT(column_name) is smaller than COUNT(*)

The column count excludes rows where that column is NULL. Use COUNT(*) to count every row matched by the query.

DISTINCT name, year still shows a name more than once

DISTINCT compares the complete pair of selected values. Select only name if uniqueness is required for names alone.

A numeric condition returns unexpected rows

Run a basic select first and inspect the stored values. Then verify the column, threshold, and operator in a condition such as year > 1980.

Key points

• SELECT retrieves data and produces a result set without modifying the table.
• COUNT(*) returns one summary row containing the number of matched rows.
• COUNT(column_name) excludes rows where that column is NULL.
• DISTINCT removes repeated selected values from output, not from stored data.
• With multiple selected columns, DISTINCT applies to the complete selected combination.
• WHERE returns only rows whose condition is true.
• Use single quotes for text literals and ordinarily leave numeric literals unquoted.

For broader querying practice, see Query a MySQL database, MySQL aggregate functions, and logical operators in WHERE conditions.