How to get total occurrences of a value within its own MySQL query?
For this, you can use subquery. Let us first create a table −
mysql> create table DemoTable
-> (
-> Value int
-> );
Query OK, 0 rows affected (0.61 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(30);
Query OK, 1 row affected (0.36 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(40);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(30);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.20 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+-------+
| Value |
+-------+
| 20 |
| 30 |
| 20 |
| 40 |
| 30 |
| 20 |
+-------+
6 rows in set (0.00 sec)
Following is the query to get total occurrence of a value −
mysql> select tbl.Value,(SELECT COUNT(*) from DemoTable where Value = tbl.Value) AS Occurrence from DemoTable tbl;
This will produce the following output −
+-------+------------+
| Value | Occurrence |
+-------+------------+
| 20 | 3 |
| 30 | 2 |
| 20 | 3 |
| 40 | 1 |
| 30 | 2 |
| 20 | 3 |
+-------+------------+
6 rows in set (0.00 sec)
Published on 11-Jul-2019 16:14:30
