#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2001
    Location
    Oslo
    Posts
    1,516
    Rep Power
    14

    Calculating sales ranking


    I was wondering if anyone has some ideas on this. Based on a typical products/orders model I need to figure out the sales rank for a given product. Now, I can't imagine a query that can achieve this (this is to be realized on Oracle), so the solution I currently imagine is storing the ranking in table, and have a trigger update the ranking (if appropriate) every time a sale is made, but am I missing something? Is it possible to derive the ranking from the data using some sort of trick? Can I model this in some other wa to achieve this?
    --
    Regards
    André Nćss

    Puritanism: The haunting fear that someone, somewhere may be having fun
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    For me a typical products/orders model usually involves at least 3 tables: products,orders,and order details.

    The "order_details" table of course is the line item for the main order, meaning that since an order can consist of several different products, and quantities of each, we tie that together by having a table with a row for every single item in the order, tallying up the quantities, thus there might be multiple rows, with different products, for any one order ID:
    Code:
    SELECT * FROM order_details;
    
    +------+--------+----------+
    | oid  | prodid | quantity |
    +------+--------+----------+
    |    2 |     44 |       55 |
    |    2 |     23 |        8 |
    |    2 |     12 |      250 |
    |    3 |     44 |        2 |
    |    3 |     68 |        3 |
    +------+--------+----------+
    You can just make a composite primary key from "oid" (Order ID), and "prodid" (Product ID), since no order should have two lines of the same item. It's up to you to determing whether you want the sales amount to appear in this table, or to be a "calculated field", thus only appearing in the view you create for invoices.

    Anyway, now it becomes very simple to get rankings of products sold:
    Code:
    SELECT prodid,SUM(quantity) AS ranking from order_details GROUP BY prodid ORDER by ranking DESC;
    This would return a list of product ids, and the total quantity sold for each product id, ordered from greatest to least. From here, it should be easy to assign a numbered ranking. If you want a ranking based on amount of sales for each product, you would obviously have to either calculate that within the ranking query, or have a column for sales amount in the order_details table. Fortunately, since the table is very compact, using only integers, your query should perform very nicely. If you just want to stop here, you can use the programming environment to take the row output and figure out a numbered rank from this query. But, if you're like me, you want to do as much inside SQL as possible: Make a view (let's call it view_rank") out of this query, and then, for any one product, it is very easy to find its current rank, by doing another query(subquery). Let's say we want the rank for product number 22:
    Code:
    SELECT (COUNT(*)+1) AS rank FROM view_rank WHERE ranking > (SELECT ranking FROM view_rank WHERE prodid = 22);
    Now, we have just counted the number of products which have a higher ranking than product 22. If there are 5 products which have sold more than product 22, then it is sixth in rank. Yes, we add 1 to COUNT(*), because otherwise the top product would have a ranking of zero, which would be completely understandable to us computer geeks, but not to the general purchasing public .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jul 2001
    Location
    Oslo
    Posts
    1,516
    Rep Power
    14
    Ah, that's a brilliant. Thanks a bunch
    --
    Regards
    André Nćss

    Puritanism: The haunting fear that someone, somewhere may be having fun

IMN logo majestic logo threadwatch logo seochat tools logo