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

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
Senior Apprentice

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? 

Senior Apprentice

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!