SQL SELECT COUNT

Introduction

In this chapter, we will learn how to use the SELECT COUNT statement in SQL. The SELECT COUNT statement is used to return the number of rows that match a specified condition. This is useful for obtaining summary statistics and performing aggregate calculations. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the SELECT COUNT statement effectively.

What is the SELECT COUNT Statement?

The SELECT COUNT statement is used to count the number of rows in a table or the number of rows that match a specific condition. It is commonly used in conjunction with the GROUP BY and HAVING clauses to generate aggregate statistics.

Syntax for SELECT COUNT

Basic Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

• column_name: The column you want to count (use * to count all rows).
• table_name: The name of the table from which you want to count rows.
• condition: The condition that must be met for a row to be counted (optional).

Example

Assume we have a table named employees:

SELECT COUNT(*)
FROM employees;

This command returns the total number of rows in the employees table.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1),
('Sita', 'Patel', 'sita.patel@example.com', 2),
('Arjun', 'Singh', 'arjun.singh@example.com', 1),
('Priya', 'Sharma', 'priya.sharma@example.com', 2),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3);

3. Count Total Rows

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

SELECT COUNT(*)
FROM employees;

Output

COUNT(*)
5

4. Count Rows with a Specific Condition

To count the number of rows where the last_name is 'Kumar':

SELECT COUNT(*)
FROM employees
WHERE last_name = 'Kumar';

Output

COUNT(*)
2

5. Count Unique Values

To count the number of unique department_id values:

SELECT COUNT(DISTINCT department_id)
FROM employees;

Output

COUNT(DISTINCT department_id)
3

Using COUNT with GROUP BY Clause

The COUNT function is often used with the GROUP BY clause to count the number of rows for each group.

Syntax

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

Example

To count the number of employees in each department:

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

Output

department_id	COUNT(*)
1	2
2	2
3	1

Using COUNT with HAVING Clause

The HAVING clause is used to filter groups based on the result of aggregate functions like COUNT.

Syntax

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING condition;

Example

To count the number of departments with more than 1 employee:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

Output

department_id	COUNT(*)
1	2
2	2

Conclusion

The SELECT COUNT statement is used for counting rows in a table, either across the entire table or based on specific conditions. It is particularly useful for generating summary statistics and performing aggregate calculations. This chapter covered the basic syntax, counting total rows, counting rows with specific conditions, counting unique values, and using COUNT with GROUP BY and HAVING clauses. Understanding how to use the SELECT COUNT statement effectively will enhance your ability to analyze and summarize your database data.