How to use COUNT(*) to return a single row instead of multiple?


You need to use GROUP BY with COUNT(*) for this to group the values and display the count eliminating multiple values. Let us first create a table:

mysql> create table DemoTable (Value int);
Query OK, 0 rows affected (0.55 sec)

Following is the query to insert some records in the table using insert command:

mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(30);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(40);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values(60);
Query OK, 1 row affected (0.25 sec)

Following is the query to display records from the table using select command:

mysql> select *from DemoTable;

This will produce the following output:

+-------+
| Value |
+-------+
|    10 |
|    20 |
|    10 |
|    30 |
|    10 |
|    20 |
|    40 |
|    10 |
|    20 |
|    20 |
|    60 |
+-------+
11 rows in set (0.00 sec)

Following is the query to return the count of similar records i.e. 10, 20, 30, 40, 60 in our case:

mysql> SELECT COUNT(*) FROM (SELECT *FROM DemoTable GROUP BY Value) AS tbl;

This will produce the following output:

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

Updated on: 30-Jul-2019

661 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements