MySQL Select where Count is greater than one [Solved]

In this article, we will be looking into the count() function with select statement and conditions.

Table of Contents

1. Select where count is greater than one : using HAVING Clause
2. Select where count is greater than one : using JOINS
3. Select where count is greater than one : using Exists

Let us get started by making the data. We will be creating the table customer_data and adding data to it.

CREATE TABLE customer_data (
    customer_id INT ,
    customer_name VARCHAR(255),
    customer_address VARCHAR(255),
    customer_level VARCHAR(50),
    mode_of_shopping VARCHAR(50)
);
INSERT INTO customer_table (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"),	
(3,"Richa","78 Singapore","Silver","Online"),
(2,"Henric","255 Italy","Silver","Online"),
(4,"Margit","587 California","Gold","Online"),	
(5,"Rohit","18 Singapore","Silver","Online"),
(6,"Ramya","43 Germany","Gold","Online"),
(7,"Avisha","258 Germany","Silver","Online"),
(5,"Rohit","899 Bangladesh","Bronze","AtShop");

We will see what got into the customer_data table by executing:

SELECT * FROM customer_data;

Output:-

Select where count is greater than one : using HAVING Clause

This section will talk about select statements where the count is greater than one using the HAVING clause. 

Syntax:-

SELECT <your_column_name> FROM <your_table> GROUP BY <your_column_name> HAVING COUNT <another_column_name>

Example:-

Let us Get the details of the customers who had more than one distinct mode of shopping (who shopped online and at the shop).

Observe the below query for the solution.

SELECT 
    cd1.*
FROM
    customer_data cd1
WHERE
    cd1.customer_id IN (SELECT 
            customer_id
        FROM
            customer_data
        GROUP BY customer_id
        HAVING COUNT(DISTINCT mode_of_shopping) > 1)
ORDER BY customer_name;

Output:-

Explanation:-

1. The above query can be divided into two parts inner-query and outer-query.
2. The inner-query will get all the customer_id from customer_data table Grouped By customer_id and Having distinct mode_of_shopping > 1
3. The outer-query will then get all the data corresponding to customer_id, matching with customer_id in inner-query.

In the output-figure 2, we can see that only customer_id -> 2, 3 and 5 are there in the result. Though customer_id=4 also has more than one mode_of_shopping, still is not included in the results as mode_of_shopping is not distinct. See the below image (figure 3) which shows data corresponding to customer_id=4.

Select where count is greater than one : using JOINS

In this section, again, we will be solving the same problem: Get the details of the customers who had more than one distinct mode of shopping, that is, who shopped both online and at the shop.

Observe the below query for the solution, including JOINs.

SELECT 
    cd1.*
FROM
    (SELECT 
        customer_id,
            COUNT(DISTINCT mode_of_shopping) AS countOfModes
    FROM
        customer_data
    GROUP BY customer_id) AS cd2,
    customer_data cd1
WHERE
    cd2.countOfModes > 1
        AND cd1.customer_id = cd2.customer_id
ORDER BY cd1.customer_name;

Output:-

Explanation:-

As we can see the same output is there and result set includes the rows with customer_id 2, 3 and 5 .

1. This solution includes a sub-query within the main-query.
2. The sub-query gets the customer_id, count of mode_of_shopping from customer_data table grouped by customer_id.
3. countOfModes is the alias name for the number of mode_of_shopping in the sub-query per customer_id.
4. The main-query gets all the data from customer_data  table corresponding to customer_id matching with sub-query. JOIN is made on the sub-query and the main-query ON customer_id.
5. Finally, filter the results for only countOfModes > 1.

Select where count is greater than one : using Exists

Let us now see how to use select where count greater is than one for any column using EXISTS.

Get all the details of customers who shopped online and at the shop. Observe the below query for solution.

SELECT 
    cd1.*
FROM
    customer_data cd1
WHERE 
    EXISTS( SELECT 
            *
        FROM
            customer_data cd2
        WHERE
                cd1.mode_of_shopping != cd2.mode_of_shopping
                AND cd1.customer_id = cd2.customer_id)
ORDER BY cd1.customer_id;

Output:-

Explanation:-

Here we are using subquery with EXISTS. When we use EXISTS with sub-query in MySQL, it will return the rows only if the Exists subquery is TRUE and vice-versa.

Since in our solution the sub-query is TRUE, the Exists will return the rows with matching customer_id and non-matching mode_of_shopping. Finally, get all the details in the outer-query corresponding to the Exists’ results from the inner sub-query.

KNOW MORE ABOUT COUNT AND HOW TO USE IT

 We hope this article helped you to with Select where Count is greater than one. Good Luck !!!