Yeah, I already got that far but it's just short of what I need as an end result.
Is there a way to generate a "line number" for a query return within the returned rows?? For instance, if I return 5 rows from a query, is there a command or function I can put in a query to add a column that contains the row number returned? Select somecommand(),blah, blew from table where blah > 1; +-----------+-----------+-----------+ | row | blah | blew | +-----------+-----------+-----------+ | row | blah | blew | | row | blah | blew | | row | blah | blew | | row | blah | blew | Jeff > -----Original Message----- > From: Gabriel Ricard [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 16, 2003 11:40 AM > To: Jeff McKeon > Cc: [EMAIL PROTECTED] > Subject: Re: Challenging query.... > > > 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/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
