having group by sum collase

문제:

Products 테이블:

ProductID	ProductName	Category	Price
1	Laptop	Electronics	1000
2	Smartphone	Electronics	800
3	Headphones	Electronics	150
4	Coffee Maker	Home	200
5	Blender	Home	100

 

Orders 테이블:

OrderID	ProductID	OrderDate	Quantity	CustomerID
101	1	2024-02-01	2	1
102	3	2024-02-02	1	2
103	2	2024-02-03	1	1
104	4	2024-02-04	3	3
105	1	2024-02-05	1	2
106	5	2024-02-06	2	3

Customers 테이블:

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA

요구사항:

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.
   

   CustomerName	TotalAmount	OrderCount
   Alice	2600	3
   Bob	950	2
   Charlie	800	2

2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.  

   Category	Top_Product	TotalSold
   Electronics	Laptop	3
   Home	Coffee Maker	3

 

SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalAmount, 
    COUNT(o.OrderID) AS OrderCount
FROM Orders o
JOIN Products p ON o.ProductID = p.ProductID
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerName;

 

Orders 테이블을 Products 테이블과 Customers 테이블에 JOIN하여 고객별로 주문한 제품의 가격과 수량을 가져옵니다.

 

 

SUM(p.Price * o.Quantity)를 사용하여 고객별 총 구매 금액(TotalAmount)을 계산합니다.

 

COUNT(o.OrderID)를 사용하여 고객이 한 총 주문 수(OrderCount)를 계산합니다.

 

GROUP BY c.CustomerName을 사용하여 고객별로 그룹화하여 결과를 집계합니다.

 

 

WITH CategorySales AS (
    SELECT 
        p.Category, 
        p.ProductName, 
        SUM(o.Quantity) AS TotalSold,
        RANK() OVER (PARTITION BY p.Category ORDER BY SUM(o.Quantity) DESC) AS rnk
    FROM Orders o
    JOIN Products p ON o.ProductID = p.ProductID
    GROUP BY p.Category, p.ProductName
)
SELECT Category, ProductName, TotalSold
FROM CategorySales
WHERE rnk = 1;

 

WITH CategorySales AS (...) (공통 테이블 표현식 CTE)

• Orders 테이블과 Products 테이블을 JOIN하여 각 제품의 총 판매량(SUM(o.Quantity) AS TotalSold)을 구합니다.
• GROUP BY p.Category, p.ProductName를 사용하여 제품별로 그룹화합니다.
• RANK() OVER (PARTITION BY p.Category ORDER BY SUM(o.Quantity) DESC) AS rnk를 사용하여 카테고리별 판매량 순위를 매깁니다. RANK()는 동일한 판매량을 가진 제품이 있을 경우 같은 순위를 부여합니다.

SELECT문에서 rnk = 1인 제품만 선택

• 가장 많이 팔린 제품만 결과로 출력됩니다.