SQL SELECT COUNT() (original) (raw)

Last Updated : 23 Jul, 2025

In SQL, the **SELECT statement is a fundamental tool for retrieving data from a **database. When paired with the **COUNT() function, it becomes even more powerful, enabling efficient **data aggregation and **analysis. This article provides a detailed explanation of **SELECT and **COUNT() in SQL, including syntax, examples, and practical use cases. Whether we’re filtering rows, counting **non-NULL values, or analyzing **distinct entries, this article will help us master these essential SQL techniques.

What is SELECT in SQL?

The SELECT statement is used to retrieve **specific columns or **rows from a table based on **defined conditions. It serves as the foundation of most SQL queries, allowing users to **extract and **filter data for analysis. Using select statements we can also apply filters using different clauses like **where, **Group By, **Having, etc.

**Syntax

SELECT <COLUMN_NAME>

FROM <TABLE_NAME>

WHERE ;

**What is COUNT() in SQL?

The **COUNT() function is an aggregate function used to count the number of rows available in a table or the number of rows that match condition criteria. It can count:

• All rows, using **COUNT(*).
• Rows matching a specific condition, using **COUNT(column_name).
• Unique values in a column, using **COUNT(DISTINCT column_name).

**Syntax

SELECT COUNT(column_name)

FROM <TABLE_NAME>

WHERE ;

Examples of SELECT with COUNT

To demonstrate the use of **SELECT with **COUNT, we will work with a sample table named **Employee.

Step 1: Create the Employee Table

CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Phone_No VARCHAR(15),
Email VARCHAR(100),
Salary INT
);

Step 2: Insert Data into the Employee Table

INSERT INTO Employee (Emp_ID, Emp_Name, Phone_No, Email, Salary) VALUES
(100, 'Ram', '000000', 'ram@gmail.com', 10000),
(101, 'Shyam', '11111111', 'shyam@gmail.com', 12000),
(102, 'Mohan', '22222', NULL, 10000),
(103, 'Sohan', NULL, 'sohan@gmail.com', 15000),
(104, 'Rakesh', '44444', NULL, 18000),
(105, 'Suresh', '55555', NULL, 16000),
(106, 'Mukesh', NULL, 'mukesh@gmail.com', 17000),
(107, 'Mina', '77777', NULL, 20000);

Step 3: View the Employee Table

SELECT * FROM Employee;

**Output

**Example 1: Count Total Rows in a Table

In this example, we apply the count function on the **Emp_id column to count all rows in the **Employee table which returns the number of rows that are not null

**Query:

SELECT COUNT(Emp_id) FROM EMPLOYEE

**Output

**Example 2: Use, of AS keyword with Count

In this example, the **AS keyword is used to assign a custom alias, Number_of_Employee, to the output column of the **COUNT() function. This improves the readability of the result by giving a meaningful **header name to the count of **employee IDs.

**Query:

SELECT COUNT (Emp_id) AS Number_of_Employee FROM EMPLOYEE

**Output

**Example 3: Count Non-NULL Values in a Column

In this example, the **COUNT() function is applied to the **Phone_No**column to count rows that are **not NULL. This helps determine how many employees have a phone number recorded in the **database.

**Query:

SELECT COUNT(Phone) AS Number_Available FROM EMPLOYEE

**Output

**Example 4: Count Rows Based on a Condition

In this example, we will count the number of employees whose salary is **more than and equal to 15000

**Query:

SELECT COUNT(Salary) AS SALARY
FROM EMPLOYEE
WHERE SALARY >=15000

**Output

**Example 5: Count Rows Using a Subquery

In this example, a subquery is used within the WHERE clause to calculate the average salary using the **AVG() function. The main query then counts the number of employees whose salary is greater than this average, using the **COUNT(*) function

**Query:

SELECT COUNT(*) AS Above_Average_Salary
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

**Output

**Example 6: Count with Distinct

In this example, the DISTINCT keyword ensures that only unique (non-duplicate) values in the**Salary** column are counted. This is useful for identifying the number of distinct **salary amounts in the dataset.

**Query:

SELECT COUNT(DISTINCT Salary) AS Distinct_Salary FROM EMPLOYEE;

**Output

Conclusion

The SELECT statement in **SQL is a **flexible and **essential tool for **retrieving and **manipulating data from the **database. When we use the **COUNT() function with Select, it becomes more powerful, allowing us to perform data analysis and obtain valuable required data. The **COUNT() function can also handle null values while counting rows and finding distinct values from the column. Mastering these functions will significantly improve our ability to work with databases and perform **complex data analysis.