Get sum of all records from result of union query-CodePudding

I have 3 different type of vehicles tables belonging to a passenger vehicle category. I want to get total number of vehicles for passenger category vehicle with status as running.

table name -> column names
cars -> id, status, category_id and so on...
vans -> id, status, category_id and so on...
buses -> id, status, category_id and so on...

When I try to get just ids and take overall count, I'm getting incorrect result.

select count(*) from (
select id from cars where category_id = 1 and status = 'RUNNING' -- result => id =3
UNION
select id from vans where category_id = 1 and status = 'RUNNING' -- result => id =4
UNION
select id from buses where category_id = 1 and status = 'RUNNING' -- result => id =3
    ) x;

Overall result is coming 2. however expected result =3

When I try with the following approach, the following query is giving correct result but taking long time to execute.

select count(*) from (
select count(*) from cars where category_id = 1 and status = 'NEW'
UNION
select count(*) from vans where category_id = 1 and status = 'NEW'
UNION
select count(*) from buses where category_id = 1 and status = 'NEW'
    ) x;

so just to avoid any performance issues, Can anyone suggest a better solution for this problem.

CodePudding user response：

You should use UNION ALL instead of UNION:

select count(*) from (
select id from cars where category_id = 1 and status = 'RUNNING' -- result => id =3
UNION ALL
select id from vans where category_id = 1 and status = 'RUNNING' -- result => id =4
UNION ALL
select id from buses where category_id = 1 and status = 'RUNNING' -- result => id =3
    ) x;