SQL SELECT COUNT()

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 <CONDITIONS>;

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 <CONDITIONS>;

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.