Query to group by based on partial count match
Dear experts,
Here is a challenge question.
I have a table a on Oracle 11i – this is how it is defined.
desc a
Name Null Type
------------------------------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STORE_NUMBER NOT NULL CHAR(9)
ASSORTMENT_NUMBER NOT NULL CHAR(9)
ITEM_NUMBER NOT NULL CHAR(9)
DESCRIPTION CHAR(1000)
UPC_CODE NOT NULL CHAR(15)
STOCK_FLAG CHAR(1)
AVG_RTL_PRICE CHAR(16)
SALES_MOVEMENT CHAR(16)
PROD_STYLE CHAR(50)
COLOR CHAR(50)
ATT_1 CHAR(50)
ATT_2 CHAR(50)
ATT_3 CHAR(50)
ATT_4 CHAR(50)
ATT_5 CHAR(50)
ATT_6 CHAR(50)
ATT_7 CHAR(50)
ATT_8 CHAR(50)
ATT_9 CHAR(50)
ATT_10 CHAR(50)
ATT_11 CHAR(50)
ATT_12 CHAR(50)
ATT_13 CHAR(50)
ATT_14 CHAR(50)
ATT_15 CHAR(50)
25 rows selected
Primary Key as Store_number, Assortment_number, item_number. Therefore, for a particular assortment number, one store will have no two records of the same item_number.
Now, I want to add some more custom conditions to my extract from this table; we are doing this manually on an xls file by applying some formula. Wondering if we can do this by a sql query.
Here are the conditions –
1. From table a, for any particular assortment_number, count of items belonging to a store. E.g –
Count of items Store_number
5 1011
7 1022
23 1033
12 1044
2. This is more challenging. From table a, for stores with the same count of item_numbers, list down the store numbers which have the same count of items and same items in the set under them. E.g – as below.
Count of items Store_number Item_number Match Indicator Matching Stores
5 3 123|12|1234|12|1 N
5 24 123|123|123|123|123 Y 22|234
5 21 123|451|2341|234|5100 N
5 22 123|123|123|123|123 Y 24|234
5 456 123|12|1|2|3 N
5 234 123|123|123|123|123 Y 22|24
3 45 12|123|12 Y 222|150
3 432 21|34|56 Y 123
3 222 12|123|12 Y 45|150
3 123 21|34|56 Y 432
3 150 12|123|12 Y 45|222
The above table may get positions shifted to its left due to formatting.
Appreciate your time and effort.
Thank You.