CODEWITHSUNDEEP
mysqlsql
hvtruong
hvtruong

Reputation: 170

union all two table but diff number of column

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 database2.orders WHERE number LIKE "11111111111111111" give me a result is null.

How to merge it with a query because with a query to help me process the pagination

Thank for helps !

Upvotes: 3

Views: 428

Answers (2)

Andomar
Andomar

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

Gordon Linoff
Gordon Linoff

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

Related Questions