SQL Server - Get the Total Count with the TOP 1 product

Question

I need to be able to find the total count of orders placed by a customer, but also find the top product in one query. For example in the following structure,

CREATE TABLE #Cust (CustId INT, CustName VARCHAR(50))
CREATE TABLE #Product (ProductId INT, ProductName VARCHAR(10) )
CREATE TABLE #Orders (CustId INT, ProductId INT, OrderTaken BIT)

INSERT #Cust
        ( CustId, CustName )
VALUES  ( 1, 'Paul' ),
        ( 2, 'F' ),
        ( 3, 'Francis' )

INSERT #Product
        ( ProductId, ProductName )
VALUES  ( 1, 'Table' ),
        ( 2, 'Chair' )

INSERT #Orders
        ( CustId, ProductId, OrderTaken )
VALUES  ( 1, 1, 1 ),
        ( 1, 1, 1 ),
        ( 1, 2, 1 ),
        ( 2, 1, 1 )

I have come up with a query,

SELECT * FROM #Cust AS C OUTER APPLY 
( 
    SELECT TOP 1 SQ.ProductId, SUM(SQ.TotalCount) AS TotalQty FROM 
    (
        SELECT O.ProductId, COUNT(*) TotalCount 
        FROM #Orders AS O WHERE O.CustId = C.CustId 
        GROUP BY O.CustId , O.ProductId
    ) SQ 
    GROUP BY SQ.ProductId 
) X

But, that is not giving me the result I am looking for, for Paul it is giving me the correct ProductId, but a count of that product alone.

I want the a Query to return,

CustId  |   CustName    |   ProductId   |   TotalQty
--------+---------------+---------------+------------
1       |   Paul        |   1           |   3
2       |   F           |   1           |   1
3       |   Francis     |   NULL        |   NULL

Answer 1

One option is with the WITH TiES clause

Select Top 1 with ties 
       CustID
      ,CustName
      ,ProductId
      ,TotalQty
 From (
        Select  C.CustID
               ,C.CustName
               ,O.ProductId
               ,TotalQty = count(O.CustId) over (Partition By O.CustID)
               ,ProdCount = count(O.CustId) over (Partition By O.CustID,O.ProductID)
         From  #Cust  C
         Left  Join #Orders O on C.CustID=O.CustId
      ) A
 Order by Row_Number() over (Partition By CustID Order by ProdCount Desc) 

Returns

CustID  CustName    ProductId   TotalQty
1       Paul        1           3
2       F           1           1
3       Francis     NULL        0