Kendall Bennett <[EMAIL PROTECTED]> wrote on 04/14/2005 06:55:02 PM:
> Hi Guys, > > I am trying to work out if it is possible to create a select statement > that will sum table data into columns with different criteria and have > it all in one select statement. For instance I have the following that > computes the quantity of a product sold within the last 60 days: > > select op.products_id, sum(op.products_quantity) as products_sold_60 > from orders_products op, orders o > where op.orders_id = o.orders_id > and to_days(o.date_purchased) > (to_days(now()) - 60) > GROUP BY op.products_id > > I would like to list both the total quantity of product sold as well as > the quantity sold in the last 60 days in the same columns. I want to do > this as a single select statement, so I can easily format the data in a > tabular control without needing to write a whole subclass for the > control. No matter what I do, I can't figure out how to sum two columns > separately! Ie: I want to join the following into a single statement > with the above: > > select op.products_id, sum(op.products_quantity) as products_sold > from orders_products op, orders o > where op.orders_id = o.orders_id > GROUP BY op.products_id > > Any suggestions? > > Regards, > > -- > Kendall Bennett > Chief Executive Officer > SciTech Software, Inc. > Phone: (530) 894 8400 > http://www.scitechsoft.com > > ~ SciTech SNAP - The future of device driver technology! ~ > Try this: select op.products_id , sum(op.products_quantity) as products_sold , sum(if(to_days(o.date_purchased) > (to_days(now()) - 60),op.products_quantity,0) as products_sold_60 from orders_products op, orders o where op.orders_id = o.orders_id GROUP BY op.products_id There may be a faster way to write this: select op.products_id , sum(op.products_quantity) as products_sold , sum(if(o.date_purchased) > now() - INTERVAL 60 DAY),op.products_quantity,0) as products_sold_60 from orders_products op, orders o where op.orders_id = o.orders_id GROUP BY op.products_id The second way eliminated two TO_DAYS() conversions and could be MUCH faster because it might be able to use an index on date_purchased. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
