In this article, we will be looking into the count() function with select statement and conditions.
Table of Contents
- Select where count is greater than one : using HAVING Clause
- Select where count is greater than one : using JOINS
- 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:-
Frequently Asked:

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:-
- The above query can be divided into two parts inner-query and outer-query.
- The inner-query will get all the customer_id from customer_data table Grouped By customer_id and Having distinct mode_of_shopping > 1
- 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 .
- This solution includes a sub-query within the main-query.
- The sub-query gets the customer_id, count of mode_of_shopping from customer_data table grouped by customer_id.
- countOfModes is the alias name for the number of mode_of_shopping in the sub-query per customer_id.
- 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.
- 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 !!!