Reputation: 170
select count(*) as total FROM ( SELECT * FROM
database1
.orders WHERE number LIKE "11111111111111111" UNION ALL SELECT * FROM database2
.orders WHERE number LIKE "11111111111111111" ) AS b
but i got error :
The used SELECT statements have a different number of columns
because run SELECT * FROM
give me a result is null.database2
.orders WHERE number LIKE "11111111111111111"
How to merge it with a query because with a query to help me process the pagination
Thank for helps !
Upvotes: 3
Views: 428
Reputation: 238296
The orders
table in database1
probably has a different number of columns than the table by the same name in database2
.
Instead of using select *
, select the columns you're interested in, like select userid, productid, deliveryaddress, ...
. Make sure you specify the same columns in both parts of the union
.
For a count(*)
, you could choose no columns at all, and select the value 1 for each row, like:
select count(*)
from (
select 1
from database1.orders
where number like '111'
union all
select 1
from database2.orders
where number like '111'
) as SubQueryAlias
Or you can add the result of two subqueries without a union
:
select (
select count(*)
from database1.orders
where number like '111'
)
+
(
select count(*)
from database2.orders
where number like '111'
)
Upvotes: 2
Reputation: 1271241
Just do the aggregation before the union all
:
select sum(cnt) as total
FROM ((SELECT count(*) as cnt
FROM database1.orders
WHERE number LIKE '11111111111111111'
)
UNION ALL
(SELECT count(*) as cnt
FROM database2.orders
WHERE number LIKE '11111111111111111'
)
) t;
Note I changed the string delimiter to be a single quote rather than a double quote. It is good practice to use single quotes for string and date constants (and nothing else).
By the way, you can also do this using a join:
select o1.cnt1, o2.cnt1, (o1.cnt1 + o2.cnt1) as total
FROM (SELECT count(*) as cnt1
FROM database1.orders
WHERE number LIKE '11111111111111111'
) o1 cross join
(SELECT count(*) as cnt2
FROM database2.orders
WHERE number LIKE '11111111111111111'
) o2;
This makes it easier to get the individual counts for the two databases.
Upvotes: 3