Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to group by based on partial count match

Sanders_2503Sep 12 2011 — edited Sep 14 2011
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2011
Added on Sep 12 2011
8 comments
1,513 views