OVER (PARTITION question

[djj55]

Hello, SQL Server 2008 R2
I have a query that I would like to change the way I calculate non-related counts. The code works well but I was wondering if there was another way than to use (total - related).

SELECT 
    C.ClientName, 
    AM.OrderNum, 
    AM.PartNum
    ,COUNT(AM.OrderNum) OVER (PARTITION BY AM.PartNum, C.ClientName) AS ClientRelated
------------------------------------------------------------------------------------------
-- This is what I would like to change
    ,COUNT(AM.OrderNum) OVER (PARTITION BY AM.PartNum) 
        -COUNT(AM.OrderNum) OVER (PARTITION BY AM.PartNum, C.ClientName) AS NonClientRelated
------------------------------------------------------------------------------------------
		,COUNT(AM.OrderNum) OVER (PARTITION BY AM.PartNum) AS Total
FROM MainTable AM 
INNER JOIN ClientNameLookup C 
ON AM.ClientID = C.ClientID 
INNER JOIN (SELECT PartNum, COUNT(*) AS CNT 
            FROM MainTable 
            GROUP BY PartNum 
            HAVING COUNT(*) > 1 ) JA 
ON AM.PartNum = JA.PartNum
ORDER BY 1, 2

Say I have three clients, a few part numbers, and several orders per client. What I want as output is:
Related by client is the count of orders for a given part for that client
Non related by client is the count of orders for a given part for other clients
Total is count of orders for a given part
Thus for Client A there are two Part1 and others have ordered Part1 four times for a total of 6 orders.
[tt]
ClientName OrderNum PartNum ClientRelated NonClientRelated Total
Client A 1234 Part1 2 4 6
Client A 1235 Part1 2 4 6
Client A 1236 Part2 1 1 2
Client B 2131 Part3 1 0 1
Client B 2132 Part1 1 5 6
Client B 2134 Part2 1 1 2
Client C 2155 Part1 3 3 6
Client C 2511 Part1 3 3 6
Client C 2611 Part1 3 3 6
[/tt]
Notice that this is contrived data and sanatized code so I hope I did not make a mistake. The actual code works and produces the desired output, but as stated I would like a better way of calculating the count of part orders by other clients.

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

I believe Cnt from your derived table is the same count as Total, so why you're re-calculating it again?

PluralSight Learning Library

 

[djj55]

Thank you, I was focusing on the PARTITION command and missed that.

Still that leaves the original question of is there a better way than [tt]JA.CNT - COUNT(AM.OrderNum) OVER (PARTITION BY AM.PartNum, C.ClientName[/tt] to get totals for the NonClientRelated?

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!