CODEWITHSUNDEEP
sqlsql-servermaxmin
Kiwito
Kiwito

Reputation: 1

SQL MAX OF SUM AT NORTHWIND DB

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

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

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

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

I can the all using the code below. I cant use MAX with 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 Diagram

Upvotes: 0

Views: 344

Answers (2)

Eray Balkanli
Eray Balkanli

Reputation: 7960

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.

;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

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

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

You will get the MAX With SUM

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

Upvotes: 1

Related Questions