Rafael Rafael - 3 months ago 10
SQL Question

SQL select count from multiple tables

I'm a starter at SQL and I have the following tables,

ORDER_PRODUCTS
, listing the products of an order and
EXCHANGE_PRODUCTS
, listing products that will be exchanged.

Both have the same fields, and I need to make a selection counting the
amount
of products in both tables, distinguishing them by the
order_id
, does anyone knows how I can do this?

ORDER_PRODUCTS

+-----+------------+----------+---------+
| id | product_id | order_id | amount |
+-----+------------+----------+---------+
| 1 | 5 | 1 | 2 |
| 2 | 7 | 1 | 1 |
| 3 | 13 | 5 | 1 |
| 4 | 18 | 8 | 3 |
| 5 | 45 | 11 | 4 |
+-----+------------+----------+---------+


EXCHANGE_PRODUCTS

+-----+------------+----------+---------+
| id | product_id | order_id | amount |
+-----+------------+----------+---------+
| 1 | 5 | 1 | 1 |
| 2 | 7 | 1 | 2 |
| 3 | 13 | 5 | 1 |
| 4 | 3 | 8 | 2 |
| 5 | 2 | 11 | 1 |
+-----+------------+----------+---------+

Answer

You want to use union all to combine the tables and then aggregate them. I might recommend:

select order_id, sum(ordered) as ordered, sum(exchanged) as exchanged,
       sum(exchanged + ordered) as total
from ((select order_id, amount as ordered, 0 as exchanged
       from order_products
      ) union all
      (select order_id, 0 as ordered, amount as exchanged
       from exhange_products
      )
     ) oe
group by order_id;

It is important to use union all rather than union, because union removes duplicates (which can result in bad numbers). Union also incurs overhead that is unnecessary.

And, by "count amount" I assume you really mean to take the sum.