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
.
Dev Shed Forums Sponsor:
March 3rd, 2002, 09:49 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
