David,
Something like
select customer.cust_id, ;
Customer.cust_name, ;
lineitem.prod_id, ;
prod_id.prod_desc, ;
sum(lineitem.qty) ;
from Customer, product, order, orderitem ;
where ( order.cust_id = customer.cust_id ) ;
and ( lineitem.order_id = order.order_id ) ;
and ( products.prod_id = lineitem.prod_id ) ;
group by 1, 2, 3, 4
>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.
Gregory