I think the closest you can get is something like this:
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/mysql?unsub=1
>
