Which SQL Function is Used to Count the Number of Rows in a SQL Query?
FREE
To count the number of rows, we can make use of the count() SQL keyword. The COUNT() is a function that returns the number of records in a table. It is one of the aggregate functions used in SQL.
Some other examples of aggregate functions include SUM (used for adding together all the values of a specific column), AVG (used to calculate the average of the group of values), MAX (used to find the largest value in a set of data), MIN (used to find the smallest value in a set of data).
Aggregate functions are those functions that perform calculations on a group of values and return a single value as a result. Here, the count() function is applied to the group of data to return a single numeric value which represents the count of the number of records in a table.
Let's take a simple example to understand this concept: If you have a table in which the details of students are stored and if you want to find the total number of students present in the table, then you can manually scan the table and maintain the count of the students in the table, but it would be a cumbersome task to do manually if the size of the table is very large, in that situation you can make use of the COUNT() function in SQL.
How to Count the Number of Rows in a Table in SQL Server?
Let's understand how the count function works from the below image :
Here, if we apply the count function on any particular column, let's say on the MARKS column, then we can write count(Marks), and it will traverse all the rows in the MARKS column, and will increment the count by 1 each time. As we can see here, there are 2 rows in the MARKS column, containing the marks of the students and so COUNT(MARKS) will return 2 as a result.
If we don't want to provide any particular column name inside the count() function and just want to count the total number of rows in the table, then we can add * inside the count() function like this count(*) and it will by default traverse all the rows in the table. As we can see from the table, it contains 2 rows having the details of the students, so the result of the count(*) query will be 2.
Let's suppose some fields in the MARKS column are empty or NULL, and so the count() function will ignore them and will count the remaining rows having non-null entries.
Here, as we can see that the marks for the roll number 2 is NULL so the count function will not count the row having marks as NULL, and the result of the count() function will be 1.
Count() function can be easily used in SQL in conjunction with other SQL clauses as per the use case. Let's see the syntax of each case along with their examples :
Syntax of Count Function in SQL :
In the syntax, we have to specify the table name and the column name on which the count function is to be applied.
Let's understand this with the help of an example :
Suppose, you have a student_details table, having ROLL_NO, FIRST_NAME, LAST_NAME, BRANCH, and MARKS as the fields, and some data has been stored in these fields.
Now, let's see how our table's data looks by making use of the SELECT command.
Note :
* represents all the columns, which means we are selecting or retrieving all the data from the given table.
Output :
| ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | BRANCH |
|---|---|---|---|---|
| 1 | Rita | Goyal | 67 | CSE |
| 2 | Kanika | Mittal | 91 | IT |
| 3 | Jyoti | Sharma | 21 | CSE |
| 4 | Reshma | Kumari | 100 | IT |
| 5 | Jayana | Meena | 32 | Civil |
| 6 | Devansh | Kumar | 71 | Civil |
| 7 | Kareena | Bhatt | 100 | IT |
Suppose if you want to count the total number of students who have appeared in the test and have scored some marks, then the syntax of the Count function in SQL would be :
The query will return the following output on the screen :
Output :
| COUNT(MARKS) |
|---|
| 7 |
Output is a single number representing the count of students who have appeared for the test.
Now, let's consider a simple case in which you want to count the total number of rows in a student_details table, then the syntax of the Count function in Structured Query Language would be :
The query will return the following output on the screen which represents the total number of records in the table :
Output :
| COUNT(*) |
|---|
| 7 |
Let's see another case in which we will apply the COUNT() function in a table having NULL entries.
Suppose, you want to count the total number of students who have appeared in the test and have scored some marks but now our table also contains some NULL entries in the MARKS column, which represents that those students were absent and have not appeared in the test.
Firstly, let's see what our student_details table looks like along with the NULL entries.
Output :
| ROLL_NO | FIRST_NAME | LAST_NAME | MARKS | BRANCH |
|---|---|---|---|---|
| 1 | Rita | Goyal | 67 | CSE |
| 2 | Kanika | Mittal | NULL | IT |
| 3 | Jyoti | Sharma | 21 | CSE |
| 4 | Reshma | Kumari | NULL | IT |
| 5 | Jayana | Meena | NULL | Civil |
| 6 | Devansh | Kumar | 71 | Civil |
| 7 | Kareena | Bhatt | 100 | IT |
So, we can write the same Count query again to count the number of students who appeared in the test :
The query will now return the following output :
Output :
| COUNT(MARKS) |
|---|
| 4 |
As we can see from the output, the count of the total number of students who appeared for the test is now reduced to 4, as the count function ignores the NULL values and we can get the actual count of the students.
Syntax of Count Function with WHERE clause :
You can also use the Count() function along with the WHERE clause. The Count() Function with the WHERE clause shows only those records that matched the specified criteria. Let's see the syntax of count() function with WHERE clause.
Let's understand this case with the help of an example :
Suppose, you want to count the total number of students whose branch is CSE.
For this, the syntax of the Count function in SQL would be :
The query will return the following output :
Output :
| COUNT(*) |
|---|
| 2 |
As we can see, there are only 2 students who have their branch as CSE.
Syntax of Count Function with DISTINCT keyword :
COUNT() function can also be used along with the DISTINCT clause to count the unique number of records in the table. The syntax of the Count function in SQL would be :
Here, by placing the DISTINCT keyword before the column name, we are ensuring to select of unique values only from the selected column.
Coming to our example, suppose you want to count the different numbers of unique branches in the student_details table. For this, you have to write the below statement in Structured Query Language :
The query will return the following output :
Output :
| COUNT(*) |
|---|
| 3 |
As we can see from the output, we have 3 different branches namely CSE, IT, and Civil, so the count of unique branches in the student_details table is 3.
Learn more about COUNT() Function in SQL
Conclusion :
- The COUNT() function returns the number of records of the table in the output.
- The COUNT(*) function in SQL shows all the records present in the table.
- The COUNT() function with the WHERE clause returns the number of rows that matches a specified criterion.
- The COUNT() function with the DISTINCT keyword shows the number of unique rows of a column.
- The COUNT() function in SQL ignores the NULL values and doesn’t count them.
- The COUNT() function only accepts one parameter namely the column_name.
- The COUNT() function comes under Aggregate numeric functions.