David,

Try

SELECT ;
Customer.name, ;
COUNT(Lineitem.prod_id) AS ITEMCOUNT, ;
Products.prod_desc, ;
FROM Customer, Orders, Lineitem, Pproducts ;
WHERE Order.cust_id = Customer.cust_id AND ;
Lineitem.order_id = Order.order_id AND ;
Products.prod_id = Lineitem.prod_id ;
GROUP BY Customer.cust_id, Lineitem.prod_id ;
ORDER BY 2 DESC ;
INTO CURSOR CustSummary

This will yield a cursor with three rows. The customer name will appear in all three rows. If you are running a report, you can create a data grouping band on Customer.name so that it prints once and the details will print below it.

John


>I have four tables: Customer, product, order and orderitem.

>The customer may have multiple orders, each order containing multiple line items. I >want to see a summary, by product, of what the customer has ordered.

?for instance, a customer has the following orders:

>Customer: ABC Company
>Order: 1
>Item1: 12 gadgets
>Item2: 4 widgets
>Item3: 2 watzits

>Order: 2
>Item1: 3 gadgets
>Item2: 2 watzits

>Using the following SELECT, I would of course get one line item for each item:

>SELECT ...fieldlist... ;
>FROM Customer, Orders, lineitem, products, prodline ;
>WHERE order.cust_id = customer.cust_id AND ;
>lineitem.order_id = order.order_id AND ;
>products.prod_id = lineitem.prod_id ;
>INTO CURSOR CustSummary

>What I would like to view is a summary as follows:

>Customer: ABC Company
>14 gadgets
>4 widgets
>2 watzits

>Is there a way to easily do this using the SELECT command? It seems like there >would be, but maybe I'm just not seeing it.

>Thanks in advance for your help.