SQL query for Finding records where count > 1

To find the records where count >1 in SQL, we have to use the HAVING clause with GROUP BY statements. This type of query is used when we have to find out the duplicate records of the table. In this blog, we will learn how we can find records that have a count > 1.

Table of Contents:

• SQL query for finding records where count > 1
  • Find the duplicate names working in different departments
• Other Practical Examples of Group BY and Having Clause
  • Example1: Grouping by the department and Counting Employees in Each Department
  • Example 2: Finding Departments with More Than One Employee (Using HAVING Clause)
• Conclusion
• FAQs

SQL query for Finding records where count > 1

SQL stands for Structured Query Language. It is used to fetch the data from Relational databases or tables with the help of some queries.  We will use the GROUP BY and HAVING clause to solve this problem.

Let’s see an example where we will find the records where the count is greater than 1. Suppose we have a table named employees that has id, name, and department as columns and have 8 records of the employees.

Let’s have a look at the table employees.

Find the duplicate names working in different departments.

Let’s find the duplicate names in this employee table who are working in different departments.

Query:

SELECT name, COUNT(*) AS count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Output:

Explanation: We grouped all the names together with their count, with the help of GROUP BY and HAVING clause, and got the result.

Other Practical Examples of Group BY and Having Clause

Here are some practical examples of Group BY and Having clauses, which will help us to in better understanding of these commands.

Let’s have a look at the below examples:

Example1: Grouping by the department and Counting Employees in Each Department

In this example, we are going to group the records on the basis of department and count the employees in each department.

Query:

SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department;

Output:

Example 2: Finding Departments with More Than One Employee (Using HAVING Clause)

Here, in this example, we are going to find only those departments that have more than one employee, with the help of the HAVING clause of SQL. With the help of the HAVING clause, we can filter the rows from a table.

Query:

SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Output:

Conclusion

So far in this article, we have learned how we can filter the rows with duplicate values with the help of GROUP BY and HAVING clause. GROUP BY clause is used to group similar records, and HAVING clause is used to filter a group of records. If you want to excel in your career in SQL, you may refer to our SQL course. Our SQL course will help you to clear all the concepts of SQL with practical examples.

FAQs