question

Chris 2 1 avatar image
Chris 2 1 asked

Group by returns duplicate distinct records.

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

sql-server-2000
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder ·
What are you doing with the results? Are you pasting them into Excel and expect the sum of the order count column to equal the total number of orders?
0 Likes 0 ·

3 Answers

·
Chris 3 1 avatar image
Chris 3 1 answered

I am exporting to excel and the sum of the order count is to be the total number of orders. Just like you said. Eventually it will be online version only.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered 
            
SELECT [Order], [BU], [SBU], 1.0/[c2] [OrderCount] FROM(            
    SELECT ot.[Order], ot.[BU], ot.[sbu], [c2] FROM(            
        SELECT [Order], COUNT(*)[c2] FROM(            
            SELECT [Order], [BU], [SBU], COUNT(DISTINCT([Order]))[c]            
            FROM [OrderTable] o1            
            GROUP BY [Order], [BU], [SBU])d            
        GROUP BY [Order])e            
    JOIN [OrderTable] ot ON (ot.[Order] = e.[Order])            
    GROUP BY ot.[Order], ot.[BU], ot.[SBU], [c2])f
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chris 3 1 avatar image
Chris 3 1 answered

What is the [c2} I cannot figure out where it is from. Should as be added to line 3 and line 4?

SELECT [Order], [BU], [SBU], 1.0/[c2] [OrderCount] FROM(            
    SELECT ot.[Order], ot.[BU], ot.[sbu], [c2] FROM(            
        SELECT [Order], COUNT(*) as [c2] FROM(            
            SELECT [Order], [BU], [SBU], COUNT(DISTINCT([Order])) as [c]            
            FROM [OrderTable] o1            
            GROUP BY [Order], [BU], [SBU])d            
        GROUP BY [Order])e            
    JOIN [OrderTable] ot ON (ot.[Order] = e.[Order])            
    GROUP BY ot.[Order], ot.[BU], ot.[SBU], [c2])f
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.