[mySQL] solvesql 지역별 주문의 특징

Q. https://solvesql.com/problems/characteristics-of-orders/

WITH sub AS (
  SELECT region, category, COUNT(DISTINCT order_id) AS cnt
  FROM records
  GROUP BY region, category)

SELECT Region,
       SUM(CASE WHEN category = 'Furniture' THEN cnt END) AS 'Furniture',
       SUM(CASE WHEN category = 'Office Supplies' THEN cnt END) AS 'Office Supplies',
       SUM(CASE WHEN category = 'Technology' THEN cnt END) AS 'Technology'
FROM sub
GROUP BY region
ORDER BY region ASC

☑️ point

• CASE문 사용 후 컬럼이 1개만 나와야하는데 여러 컬럼으로 나뉘어 null 값이 나올 경우 -> 집계함수를 사용해서 대표값 지정!!
  (GROUP BY에서 집계함수를 사용하는 이유와 같은 개념)
• 참고 : https://velog.io/@sehyunee_/SQL-GROUP-BY에-집계함수를-써야-하는-이유

+) 아래와 같은 풀이도 가능

SELECT region AS Region,
       COUNT(DISTINCT CASE WHEN category = 'Furniture' THEN order_id ELSE NULL END) AS 'Furniture',
       COUNT(DISTINCT CASE WHEN category = 'Office Supplies' THEN order_id ELSE NULL END) AS 'Office Supplies',
       COUNT(DISTINCT CASE WHEN category = 'Technology' THEN order_id ELSE NULL END) AS 'Technology'
FROM records
GROUP BY region
ORDER BY region ASC