MySQL query to count rows with a specific column?

Let us first create a table −

mysql> create table DemoTable841(    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value varchar(100) ); Query OK, 0 rows affected (0.67 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable841(Value) values('X'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable841(Value) values('Y'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable841(Value) values('Y'); Query OK, 1 row affected (1.62 sec) mysql> insert into DemoTable841(Value) values('Z'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable841(Value) values('X'); Query OK, 1 row affected (0.81 sec) mysql> insert into DemoTable841(Value) values('Z'); Query OK, 1 row affected (0.26 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable841;

This will produce the following output −

+----+-------+ | Id | Value | +----+-------+ |  1 | X     | |  2 | Y     | |  3 | Y     | |  4 | Z     | |  5 | X     | |  6 | Z     | +----+-------+ 6 rows in set (0.00 sec)

Following is the query to count rows with a specific column in MySQL −

mysql> select Value,Concat(COUNT(Value),' Times') from DemoTable841 group by Value;

This will produce the following output −

+-------+-------------------------------+ | Value | Concat(COUNT(Value),' Times') | +-------+-------------------------------+ | X     | 2 Times                       | | Y     | 2 Times | | Z     | 2 Times | +-------+-------------------------------+ 3 rows in set (0.04 sec)