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
rorym@stripped
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
----- Original Message -----
From: "Jeff McKeon" <jmckeon@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
