Counting Rows

A common task in SQL is to count the number of rows in a table. This can be done using the COUNT function.

Using the COUNT Function

The COUNT function calculates the total number of rows in a table or the total number of non-null values in a column.

Syntax

To count a specific column, we put the name of the column inside parentheses right after COUNT .

 SELECT COUNT(column_name) 
FROM table_name; 

To count all rows in a table, we can use the * character. Note that we put the * character inside parentheses (). Like this (*) .

 SELECT COUNT(*) 
FROM table_name; 

Example

In the following table called students , we have students information with columns: id , name , age , and major .

To count the number of rows in the students table, we can use the following SQL query:

 SELECT COUNT(*) 
FROM students; 

We will get the following result:

We can see that the students table has 4 rows.

Exercise 2.1

Run the following code to count the number of rows in the students table.

Exercise 2.2

Write a SQL statement to count the number of rows in the students table.

COUNT with Alias Name

By default, the name of the column in the result is COUNT(*) . We can also give it a custom name by using the AS keyword. For example, we can rename it to Total Students :

 SELECT COUNT(*) AS 'Total Students'
FROM students; 

This will give us the following result:

Exercise 2.3

Run the following code to count the number of rows in the students table and give the result a custom name ‘Total Students’.

Note: If the alias name contains spaces, you should enclose it in quotes. Otherwise, it will give you an error.

 --- This will give you an error  because of the space in the alias name 
 
SELECT COUNT(*) AS Total Students 
FROM students;
 
Error: near "Students": syntax error 

Exercise 2.4

The following code will give you an error. Correct the alias name to fix the error. Surround the alias name with quotes. Use ‘Total Students’ instead of Total Students.

You can also use the AS keyword without quotes for the alias name if there is no space in the name.

 SELECT COUNT(*) AS TotalStudents
FROM students;