MAX and SUM function with JOINING tables-CodePudding

I have two tables which is CustomerOrder and Customers

CustomerOrder
CusOrderID      CusID     OrderTotalPrice
S01             C01       180
S02             C04       50
S03             C05       70
S04             C07       46
S05             C09       78
S05             C09       164

Customers
CusID    CusName  
C01      James
C02      Jabrial
C03      Maxi
C04      Lim
C05      Tan
C06      Rem
C07      Subaru
C08      Jay
C09      Felix

I would need an output which is the customer who spent most on buying books where the display would look like this

CusID    Name     OrderTotalPrice
C09      Felix    242

I need to use max and sum function and join those two table together and display only one Customer which spend the most. How can I write the query ? this below is my sample query which doesn't really work it's Microsoft SQL and TOP function is also not encouraged.

SELECT MAX(s.Sum_OrdTotalPrice) AS max
From  (SELECT CO.CusID,C.CusName,SUM(CO.OrderTotalPrice) AS Sum_OrdTotalPrice 
        FROM CustomerOrder CO
            INNER JOIN Customers C On CO.CusID = C.CusID
            Group by CO.CusID,C.CusName
    )s

CodePudding user response：

Order the results and take just the first row?

    SELECT TOP (1) WITH TIES
           CO.CusID,C.CusName,SUM(CO.OrderTotalPrice) AS Sum_OrdTotalPrice 
      FROM CustomerOrder CO
INNER JOIN Customers C On CO.CusID = C.CusID
  GROUP BY CO.CusID,C.CusName
  ORDER BY SUM(CO.OrderTotalPrice) DESC

EDIT 1: Changed to SQL Server Syntax, following edit to question

EDIT 2: Added WITH TIES so that multiple rows are returned if multiple customers are tied for highest spend

CodePudding user response：

Should pretty much yield the same results a MatBailie, including ties, but avoids using TOP if that is an absolute necessity

SELECT 
Results.CusID, Results.Name, Results.OrderTotalPrice 
FROM (
    SELECT 
        CustomerOrder.CusID, Customers.CusName as Name
        , SUM(CustomerOrder.OrderTotalPrice) as OrderTotalPrice
        , RANK() OVER (ORDER BY SUM(CustomerOrder.OrderTotalPrice) DESC) as SalesRank
    FROM CustomerOrder
    JOIN Customers ON Customers.CusID = CustomerOrder.CusID
    GROUP BY CustomerOrder.CusID, CusName
) Results
WHERE Results.SalesRank = 1