CODEWITHSUNDEEP
mysql
Maklee Lee
Maklee Lee

Reputation: 283

how to count total record using Union All in mysql

select count(*) from ((select count(*) from employee )
 union ALL (select count(*) from events)) as total

this is my query i am trying to find ttoal record by given two query this query

 `select count(*) from employee`

give 300 record and 

select count(*) from events

this give 100 when i try to count total record then it give always 2 record can any one tell me how to count total record by give query

Upvotes: 1

Views: 3804

Answers (3)

Shubham Verma
Shubham Verma

Reputation: 9933

Use this command:

SELECT
    COUNT(*) AS total
FROM
  (SELECT * FROM db_domains where id=695
   UNION ALL
   SELECT * FROM db_domains where id=694
  ) AS A;

Result: total: 2 ( According my sql table )

Be sure that:

1.The used SELECT statements have a same number of columns.

Otherwise you will get this error: Error Code: 1222. The used SELECT statements have a different number of columns

2.Every derived table must have its own alias. Otherwise you will get this error : Error Code: 1248. Every derived table must have its own alias

See the snapshot in MYSQL Workbench. ( I have tested on workbench ):

Mysql Workbench

enter image description here

In The last snapshot: You can see the result is: 1106

Upvotes: 0

Bahadir Tasdemir
Bahadir Tasdemir

Reputation: 10793

select 
    count(*) result.union_total 
from (
    (select 1 from table1) 
    union all 
    (select 1 from table2)
) result

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You can just add together the two counts directly, no need for a UNION query:

SELECT (SELECT COUNT(*) FROM employee) + (SELECT COUNT(*) FROM events) AS total

Note that this will work because you used UNION ALL, which retains all the records in each side of the query. If you wanted to use a UNION then it would look like this:

SELECT COUNT(*) AS total
FROM
(
    SELECT * FROM employee
    UNION ALL
    SELECT * FROM events
) t

But this would only work if the two tables have the same number (and ideally types) of columns. I would probably go with the first option in any case.

Upvotes: 2

Related Questions