How to get multiple counts in single MySQL query

This article will be taking an example table customer_data and will try to get multiple counts from this table within a single query. Topics covered:

• COUNT(*) GROUP BY
• COUNT(DISTINCT) BASED ON CONDITION

Let us get started by creating the table customer_data and inserting the data into it.

CREATE TABLE customer_data (
    customer_id INT ,
    customer_name VARCHAR(255),
    customer_address VARCHAR(255),
    customer_level VARCHAR(255),
    mode_of_shopping VARCHAR(255)
);
INSERT INTO customer_data (customer_id,customer_name,customer_address,customer_level,mode_of_shopping) 
VALUES(1,"Gustav","56A Denmark","Silver","Online"),
(2,"Henric","255 USA","Silver","AtShop"),
(3,"Richa","78 India","Silver","AtShop"),
(4,"Margit","58 Canada","Gold","Online"),	
(2,"Henric","18 Libya","Silver","AtShop"),
(2,"Henric","43 Sweden","Silver","AtShop"),
(3,"Richa","78 Singapore","Silver","Online"),
(2,"Henric","255 Italy","Silver","Online"),
(4,"Margit","587 California","Gold","Online"),	
(2,"Henric","18 Singapore","Silver","AtShop"),
(2,"Henric","43 Germany","Silver","Online"),
(3,"Richa","78 Singapore","Silver","AtShop"),
(2,"Henric","258 Germany","Silver","Online"),
(5,"Rohit","899 Bangladesh","Bronze","AtShop");

We will see how the data looks by executing SELECT * FROM customer_data

Since our data is ready, let us figure out how to get multiple counts.

COUNT(*) GROUP BY

The table (figure 1) has the data which shows that customers shopped online and at the shop. What if we want to get the count of the shopping mode for each customer along with total shopping experiences? Observe the below query for solution.

SELECT 
    customer_id,
    customer_name,
    SUM(mode_of_shopping = 'AtShop') AtshopCount,
    SUM(mode_of_shopping = 'Online') OnlineCount,
    COUNT(*) TotalShoppingExperience
FROM
    customer_data
GROUP BY customer_id , customer_name
ORDER BY customer_id;

Output:-

Explanation:-

If we see the output in figure 2, we can find out that Henric shopped 4 times at the shop while 3 times online, and his total shopping experience is 7 in the count. Here we: 

1. Grouped the data based on customer_id and customer_name.
2. Calculated the sum of mode_of_shopping = “AtShop” for each group.
3. Calculated the sum of mode_of_shopping = “Online” for each group.
4. Calculated the count of mode_of_shopping for each group.

COUNT(DISTINCT) BASED ON CONDITION

This section lets us find out how to get the count of values in a column based on different conditions. What if we want to find out the distinct number of Silver, Gold, and Bronze customers ? Observe the below query for the solution.

SELECT 
    *
FROM
    (SELECT 
        COUNT(DISTINCT customer_name) GoldCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Gold') AS GoldCustomers,
    (SELECT 
        COUNT(DISTINCT customer_name) SilverCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Silver') AS SilverCustomers,
    (SELECT 
        COUNT(DISTINCT customer_name) BronzeCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Bronze') AS BronzeCustomers;

Output:-

Explanation:-

Output in figure 3 shows that there are 3 Silver customers, 1 Gold customer, and 1 Bronze customer. Here:

1. Multiple select statements are used as sub-queries to get customers’ count based on a condition applied to column customer_level.
2. Finally, all the outputs from sub-queries ate selected are selected to be displayed via the outer select statement.

We could also include the total number of distinct customers in the same output by including one more sub-query in the above expression. Observe the below query to have a better understanding.

SELECT 
    *
FROM
    (SELECT 
        COUNT(DISTINCT customer_name) GoldCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Gold') AS GoldCustomers,
    (SELECT 
        COUNT(DISTINCT customer_name) SilverCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Silver') AS SilverCustomers,
    (SELECT 
        COUNT(DISTINCT customer_name) BronzeCustomers
    FROM
        customer_data
    WHERE
        customer_level = 'Bronze') AS BronzeCustomers,
    (SELECT 
        COUNT(DISTINCT customer_name) TotalCustomers
    FROM
        customer_data) AS TotalCustomers;

Output:-

Explanation:-

Output in figure 4 includes the total count of customers from the customer_data table.

KNOW MORE ABOUT CALCULATING COUNTS IN MYSQL QUERIES

We hope this article helped you with getting multiple counts in a single MySQL query. Good Luck !!!