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:

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.