General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only