Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
2K+ Views
Advertisements