How to Get Multiple Counts With Single Query in MySQL

MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MYSQL language is easy to use as compared to other programming languages like C, C++, Java, etc. By learning some basic commands we can work, create, and interact with the Database.

MySQL is open-source and user-friendly. It creates a database to store and manipulate the data. To perform various operations users make requests by typing specific statements. The server responds to the information from the user and Displays it on the user side.

In database management, efficiency is a parameter to pass. Counting occurrences based on different criteria is a common need, and MySQL offers a powerful solution. This article dives into the technique of getting multiple counts with a single query, using the COUNT() function in parallel with the CASE statement. Understanding and implementing this approach not only improves the performance of your queries but also provides a cleaner and more concise way to gather insightful statistics from your data.

COUNT() in MYSQL

In SQL Count( ) is an aggregate function that counts the number of rows available in a table or the number of rows that match condition criteria.

Syntax:

SELECT COUNT(coumn_name) FROM <TABLE_NAME> WHERE <CONDITION>;

• COUNT(column_name): This part specifies the column that you want to count.
• FROM <TABLE_NAME>: This part specifies the table from which you want to retrieve the column data.
• WHERE <CONDITION>: This part is optional but if you want to apply filter data then you use this. This part applies the condition while retrieving the data.

To Get Multiple Counts With Single Query in MySQL

To get multiple counts with a single query in MySQL, you can use conditional aggregation. This involves using the COUNT function along with CASE statements to count based on different conditions. Here’s an example query to show you how to get multiple counts for different conditions:

Assume you have a table named Status with a column named status. You want to get counts for records based on different status values.

Create Table:

CREATE TABLE Status ( order_id INT PRIMARY KEY, status VARCHAR(20));

This Query will create table Status with column order_id and status.

Insert Data:

INSERT INTO Status (order_id, status) VALUES
 (1, 'pending'),
(2, 'shipped'),
(3, 'shipped'),
(4, 'delivered'),
(5, 'pending'),
(6, 'delivered'),
(7, 'shipped');

Output:

Status table

Multiple Counts With Single Query

Let’s Take an Example you have a table named Status with a column named status, and you want to get counts for records based on different status values (‘pending’, ‘shipped’, ‘delivered’).

Syntax:

Multiple Condtion and Counts:

SELECT COUNT(*) AS <Your Field Name>, SUM(CASE WHEN <Condition>) AS <Your Field Name>, SUM(CASE WHEN <Condition>) AS <Your Field Name>, SUM(CASE WHEN<Condition>) AS <Your Field Name>FROM <Table Name>

Example 1: Order Status Summary Statistics

This SQL query analyzes the “Status” table, delivering key statistics such as total orders and counts for pending, shipped, and delivered statuses, aiding in status-specific insights.

Query:

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
    SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_count,
    SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered_count
FROM
    Status;

Explanation:

• COUNT(*): Gives the total number of records in the table.
• SUM(CASE WHEN status = ‘active’ THEN 1 ELSE 0 END): Counts the records where the status is ‘active’.
• SUM(CASE WHEN status = ‘inactive’ THEN 1 ELSE 0 END): Counts the records where the status is ‘inactive’.
• SUM(CASE WHEN status = ‘completed’ THEN 1 ELSE 0 END): Counts the records where the status is ‘completed’.
• You can change the conditions in the CASE statements based on your requirements. This allows you to obtain multiple Counts in a single query.

Output:

Order status output

• total_orders: Total number of data in the orders table (7 records).
• pending_count: Count of data where the status is ‘pending’ (2 records).
• shipped_count: Count of data where the status is ‘shipped’ (3 records).
• delivered_count: Count of data where the status is ‘delivered’ (2 records).

Example 2: Student Exam Result Statistics

This example involves a table named “students” with columns for student ID and exam results. By utilizing a single SQL query, it calculates and presents statistics such as the total number of students, count of failures, promotions, and passes based on their exam results. The results offer insights into the distribution of student performance.

Create Table:

CREATE TABLE students (student_id INT PRIMARY KEY, exam_result VARCHAR(10));

Create Table Student with 2 columns student_id and exam_Result.

Insert Data:

INSERT INTO students (student_id, exam_result) VALUES
(1, 'Fail'),
(2, 'Pass'),
(3, 'Fail'),
(4, 'Pass'),
(5, 'Promoted'),
(6, 'Pass'),
(7, 'Promoted'),
(8, 'Pass'),
(9, 'Pass');

Output:

Students table

Multiple Counts With Single Query

SELECT
    COUNT(*) AS Total_Students,
    SUM(CASE WHEN exam_result = 'Fail' THEN 1 ELSE 0 END) AS Failed_Student,
    SUM(CASE WHEN exam_result = 'Promoted' THEN 1 ELSE 0 END) AS Promoted_Student,
       SUM(CASE WHEN exam_result = 'Pass' THEN 1 ELSE 0 END) AS Pass_Student
FROM
    students;

Explanation:

• SUM(CASE WHEN exam_result = ‘Fail’ THEN 1 ELSE 0 END): Counts the records where the student is ‘Fail’.
• SUM(CASE WHEN exam_result = ‘Promoted’ THEN 1 ELSE 0 END)): Counts the records where the student is ‘Promoted’.
• SUM(CASE WHEN exam_result = ‘Pass’ THEN 1 ELSE 0 END): Counts the records where the student is ‘Pass’.

Output:

Multiples counts from single query output

• Total_Students: Total number of data in the Student table (9 records).
• Failed_Student: Count of data where the status is ‘Fail’ (2 records).
• Promoted_Student: Count of data where the status is ‘Promoted’ (2 records).
• Pass_Student: Count of data where the status is ‘Pass’ (5 records).

Conclusion

When you need to retrieve multiple counts based on different conditions in MySQL, you can use conditional aggregation in a single query. This uses the COUNT function in combination with CASE statements to create distinct counts for multiple conditions. The result is a straight and efficient way to gather huge count without the need for multiple queries.