Count how many rows have the same value in MySQL?

To count how many rows have the same value using the function COUNT(*) and GROUP BY. The syntax is as follows −

SELECT yourColumName1, count(*) as anyVariableName from yourTableName GROUP BY yourColumName1;

To understand the above syntax, let us first create a table. The query to create a table is as follows −

mysql> create table RowWithSameValue
   −> (
   −> StudentId int,
   −> StudentName varchar(100),
   −> StudentMarks int
   −> );
Query OK, 0 rows affected (0.55 sec)

Insert some records with same value. Here, we have added same marks for more than one student for our example. The query to insert records is as follows −

mysql> insert into RowWithSameValue values(100,'Carol',89);
Query OK, 1 row affected (0.21 sec)

mysql> insert into RowWithSameValue values(101,'Sam',89);
Query OK, 1 row affected (0.15 sec)

mysql> insert into RowWithSameValue values(102,'John',99);
Query OK, 1 row affected (0.12 sec)

mysql> insert into RowWithSameValue values(103,'Johnson',89);
Query OK, 1 row affected (0.15 sec)

Now you can display all records which we inserted above. The query to display all records is as follows −

mysql> select *from RowWithSameValue;

The following is the output −

+-----------+-------------+--------------+
| StudentId | StudentName | StudentMarks |
+-----------+-------------+--------------+
|       100 | Carol       |           89 |
|       101 | Sam         |           89 |
|       102 | John        |           99 |
|       103 | Johnson     |           89 |
+-----------+-------------+--------------+
4 rows in set (0.00 sec)

Implement the syntax we discussed in the beginning to count rows that have the same value −

mysql> SELECT StudentMarks, count(*) as SameValue from RowWithSameValue GROUP BY StudentMarks;

The following is the output that displays count of multiple values −

+--------------+-----------+
| StudentMarks | SameValue |
+--------------+-----------+
|           89 |         3 |
|           99 |         1 |
+--------------+-----------+
2 rows in set (0.00 sec)

Anvi Jain

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Find rows that have the same value on a column in MySQL?
• MySQL Select Rows where two columns do not have the same value?
• How to return rows that have the same column values in MySQL?
• Get rows that have common value from the same table with different id in MySQL
• How many public classes of the same name it can have in Java?
• Count the same value of each row in a MySQL column?
• MySQL query to select too many rows?
• How to compare two arrays to see how many same elements they have in JavaScript?
• Check how many rows are in a MySQL database table?
• When running UPDATE … datetime = NOW(); will all rows updated have the same date/ time in mysql?
• Check if two String objects have the same value in C#
• MySQL - SUM rows with same ID?
• Count rows having three or more rows with a certain value in a MySQL table
• Python - Print rows from the matrix that have same element at a given index
• How many values does javascript have for nothing?