在NorthWind DB的SQL MAX OF SUM

Q: Get the highest endorsement of employee in which product has highest endorsement for that employee in northwind db.

问:获得最高认可的员工,其中产品在northwind db中对该员工的认可度最高。

What I mean is I need to get result like this:

我的意思是我需要得到这样的结果:

Adam Iphone 131231(Total endorsement of iphone sold by only adam)

Adam Iphone 131231(仅限adam销售的iphone总代言)

Maria IPad 1233 (Total endorsement of ipad sold by only Maria which she has highest endorsement as product)

Maria IPad 1233(仅由Maria销售的ipad总代言,她作为产品获得最高认可)

I can the all using the code below. I cant use MAX with SUM.

我可以使用下面的代码。我不能使用MAX和SUM。

SELECT  E.FirstName,P.ProductName, SUM(OD.Quantity*OD.UnitPrice) AS [Toplam Satış]
FROM [Order Details] OD 
INNER JOIN Products P ON P.ProductID=OD.ProductID
INNER JOIN Orders O ON O.OrderID=OD.OrderID
INNER JOIN Employees E ON O.EmployeeID=O.EmployeeID
GROUP BY E.FirstName,P.ProductName
ORDER BY E.FirstName,P.ProductName

For the one's who doesnt have NORTHWIND 在NorthWind DB的SQL MAX OF SUM

对于那些没有北风的人

2 个解决方案

#1

First SUM(OD.Quantity*OD.UnitPrice) be a subquery,then you can use subquery getting MAX.

第一个SUM(OD.Quantity * OD.UnitPrice)是子查询,然后您可以使用子查询获取MAX。

You will get the MAX With SUM

你将获得带有SUM的MAX

SELECT x.FirstName,X.ProductName,MAX(X.Toplam Satış)
FROM
(
    SELECT  E.FirstName,P.ProductName, SUM(OD.Quantity*OD.UnitPrice) AS [Toplam Satış]
    FROM [Order Details] OD 
    INNER JOIN Products P ON P.ProductID=OD.ProductID
    INNER JOIN Orders O ON O.OrderID=OD.OrderID
    INNER JOIN Employees E ON O.EmployeeID=O.EmployeeID
    GROUP BY E.FirstName,P.ProductName
) AS X
GROUP BY X.FirstName,X.ProductName

#2

What I understood from your question is that if you have a table like below:

我从你的问题中理解的是,如果你有一个如下表:

FirstName  ProductName  TotalSale
A             Y            10
A             Z            20
B             Z            30

then you are expecting to see an output like below:

那么你期望看到如下输出:

FirstName  ProductName  TotalSale
A             Y            10
B             Z            30

Depending on that, could you please try something like that? I think using cte would help you for this problem.

根据这一点,你能尝试类似的东西吗?我认为使用cte会帮助你解决这个问题。

;with cte (FirstName,ProductName,TotalSale) as
(
   SELECT  E.FirstName,P.ProductName, SUM(OD.Quantity*OD.UnitPrice) AS [TotalSale]
   FROM [Order Details] OD 
   INNER JOIN Products P ON P.ProductID=OD.ProductID
   INNER JOIN Orders O ON O.OrderID=OD.OrderID
   INNER JOIN Employees E ON O.EmployeeID=O.EmployeeID
   GROUP BY E.FirstName,P.ProductName
   ORDER BY E.FirstName,P.ProductName
)
select cte.FirstName,cte.ProductName,MAX(cte.TotalSale)
from cte
inner join (select c.ProductName,max(c.TotalSale) as Max_sale
            from cte c
            group by c.ProductName) t on t.ProductName = cte.ProductName
                                    and t.Max_Sale = cte.TotalSale
group by cte.FirstName,cte.ProductName

#1