SQL Query to Count the Number of Rows in a Table

To count the number of rows in a table in SQL, use the COUNT() function. The SQL COUNT() Function returns the total number of rows in the table, including rows with NULL values.

The Syntax to count total number of rows in SQL is:

SELECT COUNT(column_name)
FROM table_name;

In this example, we will create a demo database and table, and then write a SQL query to get the total number of rows in that table.

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:

Create students table, using the following SQL code

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 the Number of Rows in a Table

Let’s look at the query to count the number of rows in a table in SQL using COUNT() function.

Query:

SELECT COUNT(id) FROM students;

Output:

We can even change the display name for displaying count:

Count Row with AS

Query:

SELECT COUNT(id) AS id_count FROM students;

Output:

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:

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: