Can this be done without writing 100 queries

[daddypost]

Okay, I'll try to explain this as best as I can. I have a table which houses every product we have in stock and their properties. There are four fields that I'm interested in: Prod_type,Gemstone,Retail_cat, and Meterial. I need to find unique combination of the products based on 1,2,3,or 4 of these properties. IE, one grouping would include them all, another would be just the first three, however, another might be the first two and the last one. There are a possible 16 combinations (including grouping by none,which doesn't make sense). So far I've written 15 queries and thrown them all in a table. I'm sure there is an easier way, but its done now. Here comes the hard part. Those 15 queries produced 5500 results. I also have an orders table. I need to get the top 10 items for each of the items in the aforementioned table???? Without doing 15 more separate queries with a rank() over taking just the top 10, I'm not sure how else to do it?? Am I making any sense at all?
This is the query used to get the ranking on a group by all 4:
select (rank() over
(order by sum(qty) desc)) as Rank,
a.product_id,c.prod_type,c.retail_cat,c.material_type,c.gemstone,sum(qty) total
from acntv.litem a,
acntv.sku b,
acntv.master_sku c
where a.product_id=b.product_id and
b.master_id=c.master_id and
order_date between sysdate-1 and sysdate and
litem_id=1
group by a.product_id,c.prod_type,c.retail_cat,c.material_type,c.gemstone
order by rank


It looks as if I'll have to do 14 other queries based on the different grouping combinations and then run a cursor to insert them into the first table mentioned in the appropriate rank field. If you have questions, and certainly if you have any advice, please let me know. Thanks

 

[Dagon]

I don't really understand what you're trying to do. However, it sounds like you want to group over lots of different combinations of fields. You might want to consider using the ROLLUP and/or CUBE functions for this. If you could come up with a simplified test case with some example data, we might be able to help further.

 

[daddypost]

One table:
prod_type Retail_cat Material Gemstone
1 Ring Jewelry silver ruby
2 Ring Jewelry Gold Diamond
3 Ring Jewelry NA NA
4 Bracelet NA NA Diamond
5 Bracelet NA Gold Diamond
6 Book NA NA NA

Second table houses orders
ITEM QTY Prod_type Retail_cat Material Gem
1 a 2 ring Jewelry silver ruby
2 a 2 ring jewelry silver ruby
3 b 9 ring jewelry silver opal
4 c 2 bracelet jewelry gold diam.
5 d 10 book gemstone Null Ruby


I need the top selling items for each instance in the first table. The NA's are where that particular field will not be used in grouping. IE, Row 5..I need the top 10 selling items where prod_type,material, and gem from the second table match(regardless of what retail_cat it is in). Row 3, I need the top 10 items from second table that match product_type,and retail cat(regardless of the other two fields. Again, there are 16 combinations of grouping for the first table. I hope this makes things a little clearer. If not, please ask. Thank you.

 

[Dagon]

I'm not sure if this will help, but you could use grouping sets in your query to provide the different grouping combinations. You could then identify which group they belong to using grouping functions and rank over the group to get the highest values.

For simplicity, I've assumed 3 grouping sets:

prod_type, retail_cat, material, gemstone
prod_type, material
prod_type

With those grouping sets, you could use the following query:

create table sales (sale_no number, item varchar2(10), qty NUMBER, prod_type VARCHAR2(20), retail_cat varchar2(20), 
material varchar2(20), gemstone varchar2(20));

INSERT INTO SALES VALUES (1, 'a', 2, 'ring', 'Jewelry', 'silver', 'ruby');
iNSERT INTO SALES VALUES (2, 'a', 2, 'ring',   'jewelry',   'silver',    'ruby');
iNSERT INTO SALES VALUES (3, 'b', 9, 'ring',    'jewelry',   'silver',    'opal');
iNSERT INTO SALES VALUES (4, 'c', 2, 'bracelet', 'jewelry',   'gold',      'diam');
iNSERT INTO SALES VALUES (5, 'd', 10, 'book',  'gemstone',  Null, 'Ruby');


select prod_type, retail_cat, material, gemstone, grp, qty, rank() over (partition by grp order by qty desc) 
from (select prod_type, retail_cat, material, gemstone, 
case when grouping(retail_cat)=1 and grouping(gemstone) =1 and grouping(material)=1  then 'Grp 3'
     when grouping(retail_cat)=1 and grouping(gemstone) =1 then 'Grp 2'
	 else 'Grp 1' end as grp, sum(qty) as qty
from sales
group by grouping sets(
(prod_type, retail_cat, material, gemstone),
(prod_type, material),
(prod_type)))
order by grp