그룹필터링 , 정렬, 조회 개수 지정

1. HAVING: 분류된 그룹들중 특정 그룹만 가져옴

 

2. ORDERBY:  ASC(오름차순 정렬), DESC (내림차순 정렬)

 

3. LIMIT: 가져올 레코드를 제한하는 구문

 

-- 36000원 이상인 결제 수단 조회! 
SELECT 
 ptype AS "결제 수단",  -- 복습: GROUP BY는 그룹화된 컬럼만 할수 있다.
 ROUND(AVG(amount),2) AS" 평균 결제 금액"
FROM 
 payments 
GROUP BY
 ptype
 HAVING -- 그룹 필터링: 그룹화된 것들을 조건으로 선별! 
  AVG(amount) >= 36000
;


-- 상품 이름과 가격을 기준으로, 누적 판매정보(판매량, 매출)를 조회
SELECT                                     -- 4
  products.name                             AS "상품명",
  products.price                            AS "가격",
  SUM(order_details.count)                  AS "누적 판매량",
  SUM(products.price * order_details.count) AS "누적 매출"
FROM                                       -- 1
  products
LEFT JOIN order_details ON                 -- 2
  order_details.product_id = products.id
GROUP BY                                   -- 3
  products.name,
  products.price

-- (a) 누적 매출이 35,000원 이상인 상품을 조회하시오.

SELECT                                     -- 4
  products.name                             AS "상품명",
  products.price                            AS "가격",
  SUM(order_details.count)                  AS "누적 판매량",
  SUM(products.price * order_details.count) AS "누적 매출"
FROM                                       -- 1
  products
LEFT JOIN order_details ON                 -- 2
  order_details.product_id = products.id
GROUP BY                                   -- 3
  products.name,
  products.price
HAVING 
  -- 누적 매출이 35,000 이상인 것 
  SUM(products.price * order_details.count)  >= 35000
;

--(b) 누적 매출이 2만원 이상이면서, 누적 판매량도 10개 이상인 상품을 조회하시오.

  SELECT                                     -- 4
  products.name                             AS "상품명",
  products.price                            AS "가격",
  SUM(order_details.count)                  AS "누적 판매량",
  SUM(products.price * order_details.count) AS "누적 매출"
FROM                                       -- 1
  products
LEFT JOIN order_details ON                 -- 2
  order_details.product_id = products.id
GROUP BY                                   -- 3
  products.name,
  products.price
HAVING 
  -- 누적 매출이 20,000 이상인 것 
  SUM(products.price * order_details.count)  >= 20000 
  AND 
  -- 누적 판매량도 10개 이상 
  SUM(order_details.count) >= 10
 ;
 
 -- (c) 누적 매출이 없는 제품을, 가격을 기준으로 오름차순 정렬하여 조회하시오.
   SELECT                                     -- 4
  products.name                             AS "상품명",
  products.price                            AS "가격",
  SUM(order_details.count)                  AS "누적 판매량",
  SUM(products.price * order_details.count) AS "누적 매출"
FROM                                       -- 1
  products
LEFT JOIN order_details ON                 -- 2
  order_details.product_id = products.id
GROUP BY                                   -- 3
  products.name,
  products.price
HAVING 
  -- 누적 매출이 없다 ?? NULL!!
  SUM(products.price * order_details.count) IS NULL -- NULL은 IS를 붙여줘야함 
ORDER BY 
 products.price ASC -- 오름 차순
 ;
 
 
 -- (d) 누적 매출 상위 5개 상품을 조회하시오.
    SELECT                                     -- 5
  products.name                             AS "상품명",
  products.price                            AS "가격",
  SUM(order_details.count)                  AS "누적 판매량",
  SUM(products.price * order_details.count) AS "누적 매출"
FROM                                       -- 1
  products
LEFT JOIN order_details ON                 -- 2
  order_details.product_id = products.id
GROUP BY                                   -- 3
  products.name,
  products.price
HAVING                                       --4
  SUM(products.price * order_details.count) IS NOT NULL
ORDER BY                                     --6
 "누적 매출" DESC -- 내림차순 
LIMIT  -- 상위 5개 출력                       --7
 5
 ;

 

출저: 홍팍 (https://www.youtube.com/@hongpark)

이 블로그 기록은 개인 공부용 기록입니다.