Database
Enthusiast

## SQL Logic- Join the tables and aggregate based on a set of values

Hi All,

I have the below scenario to realize.

Table 1:

There is an order table with ORDER_NO and ITEM_ID. This gives all the orders with items tied. ORDER_LINE is the primary key with order_no and item_id concatenated.

 ORDER_NO ORDER_LINE ITEM_ID 11111 11111_1 1 11111 11111_2 2 11111 11111_3 3 12222 12222_2 2 13333 13333_1 1 13333 13333_4 4

There is an ITEM- STORE table with ITEM_ID AND STORE_ID. This gives item – store combinations. Each store can have many items. Each item can present in one or more stores. Many to many mapping

 STORE ITEM_ID A 1 A 2 A 3 B 2 B 4 C 1 C 2 D 1 D 2 D 3 D 4

I need a result to show:

1. Count of stores in which all the items of the given order is present.
2. This logic should happen at every order level.
3. For Example: in order 11111 there are 3 distinct items. All these 3 items are present only in Store A and D. So STORE_COUNT will be 2.
4. Second Example: For the order 13333, there are 2 item ordered and these items together are present only in Store D. Nowhere else.
 ORDER_NO ORDER_LINE ITEM_ID STORE COUNT 11111 11111_1 1 2 11111 11111_2 2 2 11111 11111_3 3 2 12222 12222_2 2 4 13333 13333_1 1 1 13333 13333_4 4 1

Can anyone help me on how can I realize this logic in SQL.

Thanks

Accepted Solutions
Junior Contributor

## Re: SQL Logic- Join the tables and aggregate based on a set of values

This is a kind of Relational Division task, you can get the counts using a query like this:

```SELECT ORDER_NO, Count(*) AS STORE_COUNT
FROM
(
SELECT ORDER_NO ,store, Cnt,
Count(i.ITEM_ID) AS i_cnt
FROM
(
SELECT ORDER_NO , ORDER_LINE , ITEM_ID,
Count(*) Over (PARTITION BY ORDER_NO) AS Cnt
FROM orders
) AS o
JOIN item_store AS i
ON o.ITEM_ID = i.ITEM_ID
GROUP BY 1,2,3
HAVING Cnt = i_cnt
) AS dt
GROUP BY 1```

Then you need to join this back to the orders table.

Depending on the actual data this calculation might consume lots of resources.

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

## Re: SQL Logic- Join the tables and aggregate based on a set of values

@dnoeth @GJColeman Could you please try to take look at it help me?

Junior Contributor

## Re: SQL Logic- Join the tables and aggregate based on a set of values

This is a kind of Relational Division task, you can get the counts using a query like this:

```SELECT ORDER_NO, Count(*) AS STORE_COUNT
FROM
(
SELECT ORDER_NO ,store, Cnt,
Count(i.ITEM_ID) AS i_cnt
FROM
(
SELECT ORDER_NO , ORDER_LINE , ITEM_ID,
Count(*) Over (PARTITION BY ORDER_NO) AS Cnt
FROM orders
) AS o
JOIN item_store AS i
ON o.ITEM_ID = i.ITEM_ID
GROUP BY 1,2,3
HAVING Cnt = i_cnt
) AS dt
GROUP BY 1```

Then you need to join this back to the orders table.

Depending on the actual data this calculation might consume lots of resources.

Enthusiast

## Re: SQL Logic- Join the tables and aggregate based on a set of values

Thanks a lot! i used the same logic and it worked just fine. You are awesome!