SQL SELECT COUNT

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

SQL SELECT COUNT

Introduction

What is the SELECT COUNT Statement?

Syntax for SELECT COUNT

Basic Syntax

Example

Step-by-Step Example

1. Create a Sample Table

2. Insert Sample Data

3. Count Total Rows

Output

4. Count Rows with a Specific Condition

Output

5. Count Unique Values

Output

Using COUNT with GROUP BY Clause

Syntax

Example

Output

Using COUNT with HAVING Clause

Syntax

Example

Output

Conclusion

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

ChatGPT + Generative AI + Prompt Engineering for Beginners

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

Building Real-Time REST APIs with Spring Boot - Blog App

Building Microservices with Spring Boot and Spring Cloud

Java Full-Stack Developer Course with Spring Boot and React JS

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

Testing Spring Boot Application with JUnit and Mockito

Spring Boot Thymeleaf Real-Time Web Application - Blog App

Master Spring Data JPA with Hibernate

Spring Boot + Apache Kafka Course - The Practical Guide

Java Testing: Mastering JUnit 5 Framework

Reactive Programming in Java: Spring WebFlux and Testing

Spring Boot + RabbitMQ Course - The Practical Guide

Functional Programming in Java (Includes Java Collections)

ChatGPT for Java Developers: Boost Your Productivity with AI

Comments

Post a Comment

Spring Boot 3 Paid Course Published for Free on my Java Guides YouTube Channel

Spring Boot 3 Paid Course Published for Free
on my Java Guides YouTube Channel