>>>>hi all,
>>>>I have a problem that I am sure can be solved with a tweak of my code. I have a table that looks like this
>>>>

>>>>order_num, unique_line_no, customer_code...
>>>>2345              123456            ABC
>>>>2345              123457            ABC
>>>>2345              123458            ABC
>>>>......
>>>>

>>>>what i am trying to do is select out from this table and group by a customer code but show the number of orders that customers has as well as the number of lines. So in this case the customer has 1 order and 3 lines. I thought that something like select distinct(order_num) as cust_num_of_orders would work but to no avail. anyone point me in the right direction please.
>>>>~M
>>>

>>>select  count(distinct Order_Num) as Orders, count(distinct Unique_Line_No) as Lines, 
>>>Customer_Code from CustomerOrders group by Customer_Code

>>
>>Naomi - this won't work. You're limited to using the DISTINCT keyword only once per query. In VFP 9, you can solve Mark's problem with derived tables, but in 8, you have to do 3 queries, one for each of the items you want to count and one to combine the results.
>>
>>Tamar
>
>As I read the problem, the second DISTINCT isn't needed. It's the number of line items for all orders. Therefore

>SELECT customer_code,COUNT(DISTINCT order_num) AS orders, COUNT(*) AS lines;
>     FROM CustomerOrders;
>     GROUP BY customer_code

looks (to me) as if it should work

Yeah, since it's all in one table, I think that would work. Usually, in this situation, you're dealing with related tables.

Tamar