Only return fields which appear a certain number of times using MySQL DISTINCT?

To return fields appearing a certain number of times using MySQL DISTINCT, you combine GROUP BY, HAVING, and COUNT() functions. The DISTINCT keyword ensures unique groupings, while HAVING filters groups based on their count.

Syntax

Following is the syntax for returning fields that appear a specific number of times −

SELECT DISTINCT columnName, COUNT(columnName)
FROM tableName
WHERE columnName LIKE 'pattern%'
GROUP BY columnName
HAVING COUNT(*) > threshold
ORDER BY columnName;

Key Components

• DISTINCT − Ensures unique groupings of the column values

• COUNT() − Counts occurrences of each grouped value

• GROUP BY − Groups rows by the specified column

• HAVING − Filters groups based on aggregate conditions

Example Implementation

Creating the Table

Let us first create a table −

mysql> create table DemoTable1500
   -> (
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.86 sec)

Inserting Sample Data

Insert some records in the table using insert command −

mysql> insert into DemoTable1500 values('Adam');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1500 values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('Mike');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('John');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1500 values('Jace');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1500 values('Jace');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1500 values('Jackie');
Query OK, 1 row affected (0.06 sec)

Displaying All Records

Display all records from the table using select statement −

mysql> select * from DemoTable1500;

This will produce the following output −

+--------+
| Name   |
+--------+
| Adam   |
| John   |
| Mike   |
| John   |
| Jace   |
| Jace   |
| Jackie |
+--------+
7 rows in set (0.00 sec)

Finding Fields with Specific Occurrence Count

Following is the query to return fields which appear more than once, specifically names starting with 'J' −

mysql> select distinct Name, count(Name)
   -> from DemoTable1500
   -> where Name LIKE 'J%'
   -> group by Name
   -> having count(*) > 1
   -> order by Name;

This will produce the following output −

+------+-------------+
| Name | count(Name) |
+------+-------------+
| Jace |           2 |
| John |           2 |
+------+-------------+
2 rows in set (0.00 sec)

How It Works

The query works by first filtering records with WHERE Name LIKE 'J%', then grouping identical names together using GROUP BY. The HAVING COUNT(*) > 1 clause filters out groups with only one occurrence, showing only names that appear multiple times.

Conclusion

Using MySQL DISTINCT with GROUP BY and HAVING clauses allows you to find fields that appear a specific number of times. This technique is useful for identifying duplicate entries, analyzing data frequency, and cleaning datasets based on occurrence patterns.