How to do a count on a MySQL union query?

To do a count on a union i.e. to get the count of the UNION result, use the below syntax −

SELECT COUNT(*) FROM ( SELECT yourColumName1 from yourTableName1 UNION SELECT yourColumName1 from yourTableName2 ) anyVariableName;

To understand the above syntax, let us create two tables with some records. The query to create a table is as follows −

mysql> create table union_Table1 -> ( -> UserId int -> ); Query OK, 0 rows affected (0.47 sec)

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

mysql> insert into union_Table1 values(1); Query OK, 1 row affected (0.18 sec) mysql> insert into union_Table1 values(10); Query OK, 1 row affected (0.12 sec) mysql> insert into union_Table1 values(20); Query OK, 1 row affected (0.09 sec)

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

mysql> select *from union_Table1;

The following is the output −

+--------+ | UserId | +--------+ | 1      | | 10     | | 20     | +--------+ 3 rows in set (0.00 sec)

The query to create a second table.

mysql> create table union_Table2 -> ( -> UserId int -> ); Query OK, 0 rows affected (0.69 sec)

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

mysql> insert into union_Table2 values(1); Query OK, 1 row affected (0.12 sec) mysql> insert into union_Table2 values(30); Query OK, 1 row affected (0.26 sec) mysql> insert into union_Table2 values(50); Query OK, 1 row affected (0.13 sec)

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

mysql> select *from union_Table2;

The following is the output −

+--------+ | UserId | +--------+ | 1      | | 30     | | 50     | +--------+ 3 rows in set (0.00 sec)

In both the tables, if any of the records is the same, then it would be considered only once. Here is the query to count on the union query.

mysql> select count(*) as UnionCount from -> ( -> select distinct UserId from union_Table1 -> union -> select distinct UserId from union_Table2 -> )tbl1;

The following is the output displaying the count.

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