sequel ace

1. 200개 이상 팔린 상품명과 그 수량을 수량 기준 내림차순으로 보여주세요.

SELECT ProductID AS `상품아이디`, ProductName AS `상품이름`, total AS `총수량`
FROM 
(
  SELECT products.ProductID, ProductName, sum(Quantity) AS total
  FROM OrderDetails AS details
  JOIN Products AS products ON details.ProductID = products.ProductID
  GROUP BY ProductID
  HAVING total >= 200
) AS groupedTable
ORDER BY total DESC
;

SELECT p.ProductID, p.ProductName, sum(o.Quantity) AS `총수량` 
FROM Products AS p
INNER JOIN OrderDetails AS o ON p.ProductID = o.ProductID
GROUP BY p.ProductID
HAVING sum(o.Quantity) >= 200
ORDER BY `총수량` DESC;

2. 많이 주문한 순으로 고객 리스트(ID, 고객명)를 구해주세요. (고객별 구매한 물품 총 갯수)

SELECT Customers.CustomerID AS `고객아이디`, CustomerName AS `고객이름`, total AS `주문량`
FROM
(
  SELECT Orders.OrderID, CustomerID, sum(Quantity) AS total
  FROM Orders
  JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
  GROUP BY CustomerID
) groupedTable
RIGHT JOIN Customers ON groupedTable.CustomerID = Customers.CustomerID
ORDER BY total DESC
;

SELECT Customers.CustomerID AS `고객아이디`, Customers.CustomerName  AS `고객이름`, sum(Quantity) AS `주문량`
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID
ORDER BY `주문량` DESC;

3. 많은 돈을 지출한 순으로 고객 리스트를 구해주세요.

SELECT Customers.CustomerID AS `고객아이디`, CustomerName AS `고객이름`, totalPrice AS `지출금액`
FROM
(
  SELECT Orders.OrderID, Orders.CustomerID, sum(Price * Quantity) AS totalPrice
  FROM Orders
  JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
  JOIN Products ON OrderDetails.ProductID = Products.ProductID
  GROUP BY CustomerID
) AS groupedTable
RIGHT JOIN Customers ON groupedTable.CustomerID = Customers.CustomerID
ORDER BY totalPrice DESC
;

SELECT Customers.CustomerID AS `고객아이디`, Customers.CustomerName AS `고객이름`, sum(Price * Quantity) AS `지출금액`
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID
ORDER BY `지출금액` DESC;