Rollup of Counts

[bobg4u]

I am trying to get the Order Count for Items and Products,and am running into a problem.
I have a query that gets the count of distinct orders for each item.(Select Item_Key,count(distinct order_no from orders group by item_key).This count needs to be rolled up to the Product Line Level. This seems fairly staright forward. But consider this situation.
Order No:1234 has 5 Items (I1,I2,I3,I4,I5). The query would show 1 Order for each of these Items. All these Items fall under one Product Line P1. When I rollup to Product Line level, it shows as 5 Orders for product line P1. But actually it should be only 1 order.
How can this be resolved?

Thanks for the help.

 

[winkid1]

This is just one of 'what do you expect to see, you're comparing apples to microwave ovens' problems. You have one order, but five items. You want to count how many times each item was ordered. Each item was ordered once. But you want a grand total at the Product Line level that says 'one order'. You cannot feasibly expect to do this in a single query. Already you've found out the caveat...you wind up counting every order with more than one item more than once.

To stick with a single query, you need to change the problem to either be:
1) How many total orders did I have? (count distinct order)
2) How many items were ordered? (count distinct items)

To do what you want, you would need more than one query. One that fetches how many items were in the order, and then an outer query that fetches how many orders there were. You're looking at a master-detail relationship report.

Create a list that fetches unique order ids, show the grand count per product, nest in a list that breaks out how many items were ordered for all orders in that product line. That should get you close. You may have to mess with it some as it may attempt to break it out all items ordered per order per product line, but I'm sure that won't be too difficult to figure out once you have this framework.