How to obtain the top N max values of a total of products in a facts table

I have a typical star schema to emulate a real, but simple, DW. My fact tables is SALES and it has, besides other products, some attributes like amount_sold, quantity_sold (per product), unit price, etc.

Now I need to introduce some data in Weka to do a Data Mining Study in which I want to characterize the people that buy the top 3 products. So, I need, perhaps a aux table that has all the distinct products in sales and the total_quantity sold for each one of them.

AUX_TOTAL_PROD_SOLD would be something like this:

Collumn_1= prod_id
Collumn_2= total_sold

So, for this purpose, I need:

1- a PL/SQL procedure to fill my aux_Table

2- obtain the 3(or another number) most sold products

I just need to find them and then I can relat开发者_运维问答e each prod_id with the client who bought it.

Do you think it's a complicated way to do this or do you think or have a better idea?

I'm testing it, but if you can I would appreciate some code/pseudo-code.

Thank you in advance

That's how I was able to achieve this:

SELECT * from
(SELECT prod_ID, SUM(s_quantity_sold) as TOTAL_SOLD
FROM SALES 
GROUP BY prod_ID
ORDER BY SUM(s_quantity_sold) DESC )
where ROWNUM <=3

it works like a charm :)

SELECT TOP 3 Product_ID, SUM(quantity_sold) AS total_sold
FROM [SALES]
GROUP BY Product_ID
ORDER BY SUM(quantity_sold) DESC

or for SQL Server

DECLARE @n int

SET @n  = 3

SELECT TOP (@n) Product_ID, SUM(quantity_sold) AS total_sold
FROM [SALES]
GROUP BY Product_ID
ORDER BY SUM(quantity_sold) DESC