On Tue, 2009-12-01 at 22:14 -0800, Bill Kendrick wrote: > > It would seem pretty simple to create the analagous table for orders: > > > > orderstats consisting of productid, date, orders > > > > and every time you complete an order, you run the query: > > > > insert into orderstats(productid, date, orders) VALUES (###, NOW(), > > QTY) on duplicate key update orders=orders+QTY > > Yes, that's great for today-on, but the moment I switch to using this > new way of recording purchases, there won't be any data. > > That is, unless I start collecting data for the next 90 days BEFORE > changing how the "top sellers" are determined. > > What I'm looking for is a way to populate a new 'order stats' table > based on the [last 90 days', or whatever, of] existing orders.
The one time command to fill the orderstats table for the first time would be INSERT into orderstats(productid, date, orders) SELECT orderitem.productid, orders.timestamp, SUM(orderitem.qty) FROM orderitem JOIN orders ON orders.id = orderitem.orderid WHERE orders.timestamp >= { 90 days ago } group by orderitem.productid, orders.timestamp assuming orders.timestamp has one-day granularity T _______________________________________________ vox-tech mailing list vox-tech@lists.lugod.org http://lists.lugod.org/mailman/listinfo/vox-tech