Count number of times value appears in particular column in MySQL?

You can use aggregate function count() with group by. The syntax is as follows.

select yourColumnName,count(*) as anyVariableName from yourtableName group by yourColumnName;

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

mysql> create table CountSameValue
-> (
-> Id int,
-> Name varchar(100),
-> Marks int
-> );
Query OK, 0 rows affected (0.70 sec)

Insert records in the table using insert command. The query is as follows.

mysql> insert into CountSameValue values(1,'Sam',67);
Query OK, 1 row affected (0.17 sec)

mysql> insert into CountSameValue values(2,'Mike',87);
Query OK, 1 row affected (0.19 sec)

mysql> insert into CountSameValue values(3,'Carol',67);
Query OK, 1 row affected (0.24 sec)

mysql> insert into CountSameValue values(4,'Bob',87);
Query OK, 1 row affected (0.18 sec)

mysql> insert into CountSameValue values(5,'John',71);
Query OK, 1 row affected (0.17 sec)

mysql> insert into CountSameValue values(6,'Adam',66);
Query OK, 1 row affected (0.18 sec)

mysql> insert into CountSameValue values(7,'David',71);
Query OK, 1 row affected (0.20 sec)

mysql> insert into CountSameValue values(8,'Maria',67);
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from CountSameValue;

The following is the output.

+------+-------+-------+
| Id   | Name  | Marks |
+------+-------+-------+
| 1    | Sam   | 67    |
| 2    | Mike  | 87    |
| 3    | Carol | 67    |
| 4    | Bob   | 87    |
| 5    | John  | 71    |
| 6    | Adam  | 66    |
| 7    | David | 71    |
| 8    | Maria | 67    |
+------+-------+-------+
8 rows in set (0.00 sec)

Here is the query to count the number of times value (marks) appears in a column. The query is as follows.

mysql> select Marks,count(*) as Total from CountSameValue group by Marks;

The following is the output.

+-------+-------+
| Marks | Total |
+-------+-------+
| 67    | 3     |
| 87    | 2     |
| 71    | 2     |
| 66    | 1     |
+-------+-------+
4 rows in set (0.00 sec)

Ankith Reddy

Updated on: 2019-07-30T22:30:24+05:30

12K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started