l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
July 7: Social gathering
Next Installfest:
Latest News:
Jun. 14: June LUGOD meeting cancelled
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)
     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


vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
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:
EDGE Tech Corp.
For donating some give-aways for our meetings.