I have a database that has several order ID numbers with their associated part numbers and Business unit and sub business units that are associated to the part numbers.
The problem is that I have to return not only the count of the distinct order numbers but I have to return the business and sub business unit. as ORder can have multiple part numbers that have different business units and/or sub business unit.
For example: Order 123 PN a4506 has business unit A1 and sub business unit S2 PN a4506 has business unit A1 and sub business unit D4 PN S3456 has business unit S3 and sub business unit of G3
My query us set up like this: BU = Business Unit and SBU is sub business unit
Select count(distinct(order)), BU, SBU from table group by order,bu,sbu
I get 3 rows returned and the order count is 3 I want the order to be split bbetween the BU and SBU if they are different. I should get the order count of 0.333 for each row and not 1 for each row. How can I accomplish this in SQL 2000