Oracle SQL - how do I make a join without making duplicates-CodePudding

So I have this table "Order" that has all the orders inside it and this table "Ship", that saves everytime a new box is shipped. The way it works is that everytime a Box ID is scanned, the table "Ship" gets that Box ID and searches inside the Order table to get complementary information to be inserted, so both tables are linked by the Box ID. The thing is, the "Order" table have multiple duplicate Box IDs, because each row is a separate order.

The problem that i'm having is that I need to find how many orders each Box ID have, and compare to how much of that Box ID are in the "Ship" table, however when I make a query to find that out, it duplicates.

Here is an Example from Order:

         *BOX ID                ROUTE             ACTIVITY*
      BX00936690000               1                  12
      BX00936690000               1                  12
      BX00936690000               1                  12
      BX00936690000               1                  12
      BX00936690000               1                  12
      BX00971770000               2                  15
      BX00971770000               2                  15

And Ship looks like this:

         *BOX ID                ROUTE             ACTIVITY         TIME*
      BX00936690000               1                  12          15:10:04
      BX00936690000               1                  12          12:30:20
      BX00971770000               2                  15          11:50:31

However, when I try this code:

select OD.BOX_ID, count(OD.BOX_ID) as total_ordered, count(EX.BOX_ID) as total_shipped
from Ship EX 
inner join Order OD on (EX.BOX_ID = OD.BOX_ID) 
where EX.BOX_ID = OD.BOX_ID
group by OD.BOX_ID

The end result is like this:

         *BOX ID             total_ordered      total_shipped*
      BX00936690000               5                  5
      BX00971770000               2                  2

When it should be like this:

        *BOX ID             total_ordered      total_shipped*
      BX00936690000               5                  2
      BX00971770000               2                  1

How this problem could be solved so I can have the expected result?

CodePudding user response：

Because you only want to count the matching rows for shipped orders you can use a correlated subquery, see if the following is what you need:

select o.Box_Id, Count(*) Total_Ordered,
  (select Count(*) from Ship s where s.Box_Id=o.Box_Id) total_shipped
from orders o
group by o.Box_Id

CodePudding user response：

you can do with the query below:

select o.box_id, o.total_order_cnt, o.total_ship_cnt
  from (
       select box_id, count(*) total_order_cnt
         from orders o
        group by 1
       ) o
  join (
       select box_id, count(*) total_ship_cnt
         from ships sh
        group by 1
       ) sh
    on o.box_id = sh.box_id

CodePudding user response：

You want to join the number of orders with the numbers of shipments. So, aggregate to get those numbers, then join.

select
  box_id,
  coalesce(o.total, 0) as ordered,
  coalesce(s.total, 0) as shipped
from
  (select box_id, count(*) as total from orders group by box_id) o
full outer join
  (select box_id, count(*) as total from shipments group by box_id) s using (box_id)
order by box_id;

If you see it guanranteed that there must be an order when there is a shipment, you can change the join to a left outer join. If you only want boxes that have both orders and shipments, make it an inner join.