Hi If you have a way to generate the query code dynamically (e.g. using a loop in C, PHP etc.), you can build a query using aliased tables :
SELECT YEAR(a.date) AS year, MONTH(a.date) AS month, SUM(a.revenue) AS cust1_rev, SUM(b.revenue) AS cust2_rev, SUM(c.revenue) AS cust3_rev FROM revenue a, revenue b, revenue c WHERE a.customer_id = 1 AND (YEAR(b.date) = YEAR(a.date) AND MONTH(b.date) = MONTH(a.date) AND b.customer_id = 2) AND (YEAR(c.date) = YEAR(c.date) AND MONTH(c.date) = MONTH(c.date) AND c.customer_id = 3) GROUP BY year, month Regards Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] "There are 10 kinds of people in this world, those who understand binary and those who don't" (Unknown) ----- Original Message ----- From: "Jeff McKeon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 16, 2003 4:21 PM Subject: Challenging query.... I have a table that contains customer revenue information. REVENUE TABLE: Date, customer name, CustomerID, revenue, quantity I need to create a query that will produce the following result Year, Month, Customer1_rev, customer2_REV, customer3_rev, etc... 2002, 01, 0, $30.00, $15.00 2002, 02, $25.00, $50.00, $10.00 2002, 03, $10.00, $25.00, $40.00 Etc.. Can this be done with a single query??? Jeff -- 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]
