Sorry, hit ctrl-s by accident and sent the email before I was done...
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 some_command(),blah, blew from table where blah > 1;
+-----------+-----------+-----------+
| row | blah | blew |
+-----------+-----------+-----------+
| 1 | blah | blew |
| 2 | blah | blew |
| 3 | blah | blew |
| 4 | blah | blew |
+-----------+-----------+-----------+
Thanks,
Jeff
> -----Original Message-----
> From: Gabriel Ricard [mailto:gabe@stripped]
> Sent: Thursday, October 16, 2003 11:40 AM
> To: Jeff McKeon
> Cc: mysql@stripped
> 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/mysql?unsub=1
> >
>
>
