You can use an IF in a SUM if you add them based on special conditions: e.g.: SUM(IF(field = 1, field_to_sum, 0))
-----Original Message----- From: Kendall Bennett [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 14. April 2005 23:55 To: mysql@lists.mysql.com Subject: Multiple sums in a single select? 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! ~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
