SQL Query to Count the Number of Rows in a Table

In this article, we are going to write an SQL query to count the number of rows in a table. For this, we will be making use of the count() function of SQL. For this article, we will be making use of the Microsoft SQL Server as our database. Learning to count the number of rows in the table will be best if we learn it by seeing through a table so let’s deep dive into the table.

Let’s do the same by building a table inside the database and counting its rows. We will first create a database called “students”.

Creating a Database:

Use the below SQL statement to create a database called students.

CREATE DATABASE students;

Using Database:

USE students;

Table Definition:

We have the following student table in our student database :

-- Create a table called 'students'
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(20)
);

-- Insert some sample data into the 'customers' table
INSERT INTO students (id, name, email, phone)
VALUES 
  (1, 'Aman Chopra', 'Aman@example.com', '123-456-7890'),
  (2, 'Aditya Arpan', 'Aditya@example.com', '987-654-3210'),
  (3, 'Shubham Thakur', 'Shubham@example.com', '555-555-5555'),
  (4, 'Naveen tulasi', 'naveen@example.com', '987-654-3210'),
  (5, 'Varsha Choudhary', 'varsha@example.com', '787-957-3657');

Output:

SQL Query To Count Number of Rows

The SQL COUNT( ) function is used to return the number of rows in a table. It is used with the Select( ) statement.

Syntax:

SELECT COUNT(column_name) from table_name;

Example: Using ‘ * ‘  we get all the rows as shown below.

Query:

SELECT * FROM students;

Query:

SELECT COUNT(id) from students;

Output:

We can even change the display name for displaying count:

Count Row with Using AS

Query:

SELECT COUNT(id) as id_count FROM students;

Output:

SQL Query To Count the Rows with HAVING Clause 

We can use the HAVING clause in the SQL query to specify a condition for the COUNT function and also we can modify the code to only show the results for num_rows where the count is greater than 1.

Query:

SELECT phone, COUNT(*) as num_rows
FROM students
GROUP BY phone
HAVING num_rows > 1;

Output:

SQL Query To Count the Rows with Order By Clause 

The ORDER BY clause is used to sort the results of a SQL query by one or more columns. When used in conjunction with the COUNT() function, the ORDER BY clause can be used to sort the results by the count of a particular column.

For example, let’s say we have a student’s table with columns id, name, email, and phone. We want to count the number of students in each phone and then sort the results in descending order by the count of students in each phone. We can use the following SQL query:

Query:

SELECT phone, COUNT(*) as num_students
FROM students
GROUP BY phone
ORDER BY num_students ASC;

Output:

Conclusion

In conclusion, the ORDER BY clause is used in SQL to sort the results of a query by one or more columns. When used with the COUNT() function, the ORDER BY clause can be used to sort the results by the count of a particular column. This allows us to sort the results of a query in ascending or descending order by the number of occurrences of a particular column value. By combining GROUP BY, COUNT(), and ORDER BY clauses, we can retrieve and sort summarized data in a meaningful way.