Skip to main content

Counting with COUNT and COUNT(DISTINCT)

Premium

In this lesson, we will be exploring the use of COUNT and COUNT(DISTINCT) aggregate functions in SQL. These functions are incredibly useful for finding the number of rows in a table, which can be useful in a variety of applications.

First, let's explore the basic use of the COUNT function. The syntax for count is simple:

SQL
SELECT COUNT(*) FROM students;

This will return the number of rows in the students table. If you only want to count a specific column, you can replace the asterisk (*) with the name of the column, for example:

SQL
SELECT COUNT(student_name) FROM students;

This will return the number of non-null values in the student_name column.

Now, let's move on to COUNT(DISTINCT). The syntax for COUNT(DISTINCT) is similar to the basic COUNT function, but it only counts unique values:

SQL
SELECT COUNT(DISTINCT country) FROM students;

This will return the number of unique countries in the students table.

The COUNT function can also be used with the GROUP BY clause to find the number of students in each country:

SQL
SELECT country, COUNT(*) FROM students GROUP BY country;

This will return a table with each country and the number of students in each country. You can also use the COUNT(DISTINCT) function with the GROUP BY clause:

SQL
SELECT country, COUNT(DISTINCT student_id) FROM students GROUP BY country;

This will return a table with each country and the number of unique students in each country.

In conclusion, the COUNT and COUNT(DISTINCT) functions are powerful tools for finding the number of rows or unique values in a table. They can be used in a variety of applications, from simple counts to more complex analyses using GROUP BY and ORDER BY.

Try it yourself

Given a table of customers, count the number of customers who have the same first name, returning the first_name and count ordered by most frequent to least frequent names.

Next Lesson