Counting Rows

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.

In SQL there is a special value called NULL. If you count a column that has null values, it will skip it.

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, we have students information with columns: id, name, age, and major.

Create a table similar to the following students table in your database.

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 you don’t use quotes for the alias name if there is a space in the name, you will get an error. So, always use quotes for the alias name if there is a space in the name.

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

This will give you an error.

 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; 

This will work fine.