SQL COUNT Function

SQL COUNT Function

The SQL COUNT function is used to count the number of rows that match a specified condition. This function is essential for performing various aggregate operations, such as counting the total number of rows, counting distinct values, or counting rows that meet specific criteria.

---

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

The COUNT function returns the number of rows that match the specified condition. The WHERE clause is optional and can be used to filter the rows before applying the COUNT function.

---

Example SQL COUNT Queries

Let's look at some examples of SQL COUNT queries using the employees table:

1. Basic COUNT Example

SELECT COUNT(*) AS num_employees
FROM employees;

This query uses the COUNT function to count the total number of rows in the employees table.

2. COUNT with WHERE Clause

SELECT COUNT(*) AS num_employees
FROM employees
WHERE department_id = 3;

This query uses the COUNT function to count the number of rows in the employees table where the department_id is 3.

3. COUNT with DISTINCT

SELECT COUNT(DISTINCT department_id) AS num_departments
FROM employees;

This query uses the COUNT function to count the number of distinct department IDs in the employees table.

4. COUNT with GROUP BY

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

This query uses the COUNT function to count the number of rows for each department in the employees table.

---

Full Example

Let's go through a complete example that includes creating a database, creating tables, inserting data, and using the COUNT function.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating Tables

In this step, we create two tables named employees and departments within the previously created database.

USE example_db;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, we define the departments table with columns for department_id and department_name. We also define the employees table with columns for employee_id, first_name, last_name, department_id, and salary, with a foreign key relationship to the departments table.

Step 3: Inserting Data into the Tables

This step involves inserting some sample data into the departments and employees tables.

INSERT INTO departments (department_name) VALUES ('HR'), ('Finance'), ('IT'), ('Marketing');

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('John', 'Doe', 1, 50000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Jane', 'Smith', 2, 60000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Alice', 'Johnson', 3, 70000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Bob', 'Brown', 4, 80000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Charlie', 'Davis', 1, 90000.00);

Here, we insert data into the departments table and then insert data into the employees table, ensuring that each employee is assigned to a department.

Step 4: Using the COUNT Function

This step involves using the COUNT function to perform various aggregate operations on the employees table.

Example 1: Counting All Employees

SELECT COUNT(*) AS num_employees
FROM employees;

This query counts the total number of rows in the employees table.

Example 2: Counting Employees in a Specific Department

SELECT COUNT(*) AS num_employees
FROM employees
WHERE department_id = 3;

This query counts the number of rows in the employees table where the department_id is 3.

Example 3: Counting Distinct Departments

SELECT COUNT(DISTINCT department_id) AS num_departments
FROM employees;

This query counts the number of distinct department IDs in the employees table.

Example 4: Counting Employees by Department

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

This query counts the number of rows for each department in the employees table.

---

Conclusion

The SQL COUNT function is a powerful tool for counting rows in a table. Understanding how to use the COUNT function and its syntax is essential for performing various aggregate operations and data analysis in a relational database.