MySQL query to count frequency of students with the same age?

You can use COUNT(*) along with GROUP BY for this. Let us first create a table −

mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentAge int    ); Query OK, 0 rows affected (0.59 sec)

Insert records in the table using insert command −

mysql> insert into DemoTable(StudentAge) values(16); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentAge) values(18); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(StudentAge) values(17); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(StudentAge) values(18); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentAge) values(18); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(StudentAge) values(19); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentAge) values(19); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(StudentAge) values(16); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentAge) values(16); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentAge) values(15); Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable;

This will produce the following output −

+-----------+------------+ | StudentId | StudentAge | +-----------+------------+ | 1         | 16         | | 2         | 17         | | 3         | 18         | | 4         | 17         | | 5         | 17         | | 6         | 17         | | 7         | 18         | | 8         | 18         | | 9         | 19         | | 10        | 19         | | 11        | 16         | | 12        | 16         | | 13        | 15         | +-----------+------------+ 13 rows in set (0.00 sec)

Following is the query to count frequency −

mysql> select StudentAge,count(*) AS `AgeFrequency` from DemoTable group by StudentAge;

This will produce the following output −

+------------+--------------+ | StudentAge | AgeFrequency | +------------+--------------+ | 16         | 3            | | 17         | 4            | | 18         | 3            | | 19         | 2            | | 15         | 1            | +------------+--------------+ 5 rows in set (0.05 sec)