SQL query for counting combinations and including entries that don't exist

Question

This is a simplified version of the table I am dealing with which is Orders

+-------------------+------------------+---------------+
| Order_Base_Number | Order_Lot_Number | Other Cols... |
+-------------------+------------------+---------------+
|                 1 |                3 |               |
|                 1 |                3 |               |
|                 1 |                4 |               |
|                 1 |                4 |               |
|                 1 |                4 |               |
|                 1 |                5 |               |
|                 2 |                3 |               |
|                 2 |                5 |               |
|                 2 |                9 |               |
|                 2 |               10 |               |
+-------------------+------------------+---------------+

What I want to do is to get a count for the unique entries base on Base and Lot numbers. I have two set of numbers, one is a set of Base numbers and the other is a set of Lot numbers. for example, lets say the two sets are Base In (1,2,3) and Lot is in (3,4,20).

I am looking for an SQL query that can return all the possible combination of (Base,Lot) from the two sets with a count that shows how many times the combination was found in the table. My problem is that I want to include all the possible combinations and if a combination is not in the Orders table, I want the count to show zero. So, the output I am looking for is something like this.

+------+-----+-----------+
| Base | Lot | Frequency |
+------+-----+-----------+
|    1 |   3 |    2      |
|    1 |   4 |    3      |
|    1 |  20 |    0      |
|    2 |   3 |    1      |
|    2 |   4 |    0      |
|    2 |  20 |    0      |
|    3 |   3 |    0      |
|    3 |   4 |    0      |
|    3 |  20 |    0      |
+------+-----+-----------+

I tried a lot of queries but never got close to this and not even sure if it can be done. Right now I am figuring out the combinations on the client side and hence I am performing too many queries to get the frequencies.

Answer 1

Perhaps the clearest way is to start with the lists as CTEs:

with bases as (
    select 1 as base from dual union all
    select 2 as base from dual union all
    select 3 as base from dual
   ),
     lots as (
    select 3 as lot from dual union all
    select 4 as lot from dual union all
    select 20 as lot from dual
   )
select b.base, l.lot, count(Order_Base_Number) as Frequency
from bases b cross join lots l left outer join
     Orders o
     on o.base = b.base and o.lot = l.lot
group by b.base, l.lot

Note that this makes the cross join explicit, purposely not using the , for a Cartesian product.

The first part of this query could also be written as something like the following (assuming that each base and lot has at least one record in the table):

with bases as (
     select distinct base
     from Orders  -- or some other table, perhaps Orders ?
     where base in (1, 2, 3)
    ),
     select distinct lot
     from Orders  -- or some other table, perhaps Lots ?
     where lot in (3, 4, 20)
    )
. . .

This is more succinct, but might result in a less efficient query.

Answer 2

I don't have Oracle to test it but this is what I would do:

CREATE TABLE pairs AS
(
SELECT DISTINCT Base.Order_Base_Number, Lot.Order_Lot_Number 
FROM ORDERS Base
CROSS JOIN ORDERS Lot
);

CREATE TABLE counts AS
(
SELECT Order_Base_Number, Order_Lot_Number, Count(*) AS C
FROM ORDERS 
GROUP BY Order_Base_Number, Order_Lot_Number
);

SELECT P.Order_Base_Number, P.Order_Lot_Number, COALESCE(C.C,0) AS [Count]
FROM Pairs P
LEFT JOIN counts C ON P.Order_Base_Number = C.Order_Base_Number 
             AND P.Order_Lot_Number = C.Order_Lot_Number

Answer 3

What you need in the innermost subquery is called CROSS JOIN, which gets cartesian products (all possible combinations) of records. That's what you get when you have neither JOIN..ON condition nor WHERE:

SELECT Base.Id as baseid, Lot.Id as lotid FROM Bases, Lots

Now put it into subquery and LEFT JOIN to the rest of your stuff:

SELECT ... FROM 
   (SELECT Base.Id as baseid, Lot.Id as lotid 
           FROM Bases, Lots) baseslots
   LEFT JOIN Orders ON Order_Base_Number = baseid, 
             Order_Lot_Number = lotid ....

With this LEFT JOIN, you'll get NULL for nonexistent combinations. Use COALESCE (or something like this) to turn them into 0.