The COUNT() function in SQL

The COUNT() function in SQL

The COUNT() function in SQL is a versatile aggregation function used to count the number of rows in a result set. It can be applied in various contexts to count rows, distinct values, or non-null values. Here’s a detailed look at how COUNT() works and how you can use it effectively.

Overview of COUNT()

The COUNT() function is used to count the number of rows in a specified table or result set. Its primary purpose is to provide a count of rows that meet a specific condition.

Syntax

Counting All Rows 

SELECT COUNT(*)
FROM table;

COUNT(*) counts all rows in the table, including those with NULL values in any column.

Counting Non-NULL Values in a Specific Column 

SELECT COUNT(column_name)
FROM table;

COUNT(column_name) counts the number of non-NULL values in the specified column.

Counting Distinct Values 

SELECT COUNT(DISTINCT column_name)
FROM table;

COUNT(DISTINCT column_name) counts the number of unique, non-NULL values in the specified column.

Examples of Using COUNT()

Example 1: Counting All Rows in a Table

Assume you have a table employees: 

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

To count the total number of rows in the employees table:

Explanation: 

SELECT COUNT(*) AS total_employees
FROM employees;

COUNT(*) counts all rows in the employees table.

Example 2: Counting Non-NULL Values in a Column

Suppose you want to count the number of employees who have a department specified (i.e., non-NULL values in the department column): 

SELECT COUNT(department) AS employees_with_department
FROM employees;

Explanation:

• COUNT(department) counts only the rows where the department column is not NULL.

Example 3: Counting Distinct Values in a Column

If you want to find out how many unique departments there are in the employees table: 

SELECT COUNT(DISTINCT department) AS unique_departments
FROM employees;

Explanation:

• COUNT(DISTINCT department) counts the number of unique, non-NULL values in the department column.

Example 4: Using COUNT() with GROUP BY

You can also use COUNT() in conjunction with the GROUP BY clause to count rows within each group. For example, to count the number of employees in each department: 

SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department;

Explanation:

• COUNT(*) counts the total number of rows for each department.
• GROUP BY department groups the rows by the department column so that COUNT() is applied to each group.

Example 5: Counting Rows with Conditions Using WHERE

To count the number of employees in a specific department, you can use the WHERE clause: 

SELECT COUNT(*) AS employees_in_sales
FROM employees
WHERE department = 'Sales';

Explanation:

• COUNT(*) counts all rows where the department column equals ‘Sales’.
• The WHERE clause filters the rows to include only those where department is ‘Sales’.

Summary

• COUNT(*) counts all rows in a table or result set.
• COUNT(column_name) counts only non-NULL values in the specified column.
• COUNT(DISTINCT column_name) counts the number of unique, non-NULL values in the specified column.
• COUNT() can be used with the GROUP BY clause to count rows within each group.
• COUNT() can be used with the WHERE clause to count rows that meet specific conditions.