SQL SELECT COUNT

SQL SELECT COUNT Statement

The SQL SELECT COUNT statement is used to count the number of rows in a database table that match a specified condition. This command is essential for obtaining summary statistics and understanding the distribution of data within tables.

---

Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• SELECT COUNT: This is the SQL keyword used to count the number of rows.
• column_name: This specifies the column to count the rows from. You can use * to count all rows.
• FROM: This is the SQL keyword used to specify the table.
• table_name: This specifies the name of the table from which to count the rows.
• WHERE condition: This specifies the condition that the rows must meet to be counted (optional).

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the SELECT COUNT statement to count the number of rows.

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 a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the SELECT COUNT Statement

This step involves counting the total number of rows in the employees table using the SELECT COUNT statement.

SELECT COUNT(*) FROM employees;

This command will return the total number of rows in the employees table.

To count the number of rows that meet a specific condition (e.g., employees hired after February 1, 2023):

SELECT COUNT(*) FROM employees
WHERE hire_date > '2023-02-01';

This command will return the number of rows in the employees table where the hire_date is after February 1, 2023.

To count the number of unique values in a column (e.g., unique last names):

SELECT COUNT(DISTINCT last_name) FROM employees;

This command will return the number of unique last names in the employees table.