SELECT YEAR(TheDate) AS Year,MONTH(TheDate) AS Month,CustomerName,CustomerID,SUM(Revenue*Quantity) AS Revenue
FROM rev
GROUP BY Year, Month
ORDER BY Year,Month,CustomerID
A table with this data:
+------------+--------------+------------+---------+----------+ | TheDate | CustomerName | CustomerID | Revenue | Quantity | +------------+--------------+------------+---------+----------+ | 2003-10-16 | Bob | 1 | 10.00 | 1 | | 2003-10-16 | Bob | 1 | 5.00 | 2 | | 2003-09-01 | Bob | 1 | 20.00 | 5 | | 2003-10-10 | Bob | 1 | 5.00 | 2 | +------------+--------------+------------+---------+----------+
Would give you something like this:
+------+-------+--------------+------------+---------+ | Year | Month | CustomerName | CustomerID | Revenue | +------+-------+--------------+------------+---------+ | 2003 | 9 | Bob | 1 | 100.00 | | 2003 | 10 | Bob | 1 | 30.00 | +------+-------+--------------+------------+---------+
Or if the Revenue field is a total, then just don't multiply it by Quantity.
So far as I know, there is no [easy?] way to generate dynamic columns in the result set like you're looking for. You can generate it the way I described and then manipulate that data into your desired format in the application layer.
- Gabriel
On Thursday, October 16, 2003, at 10:21 AM, Jeff McKeon wrote:
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]
