l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2009 Dec 01 22:31

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
Re: [vox-tech] MySQL question: making stats out of orders
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [vox-tech] MySQL question: making stats out of orders



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



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.