GROUP BY와 집계 함수

SELECT COUNT(*) AS 전체상품수,
       COUNT(category) AS 카테고리있는수,
       SUM(stock) AS 전체재고,
       AVG(price) AS 평균가격,
       MAX(price) AS 최고가,
       MIN(price) AS 최저가
FROM products;

전체상품수 | 카테고리있는수 | 전체재고 | 평균가격 | 최고가  | 최저가
-----------|----------------|----------|----------|---------|-------
6          | 5              | 150      | 75000    | 150000  | 15000

-- 예제 데이터:
-- id | category
-- 1  | 전자
-- 2  | 전자
-- 3  | 식품
-- 4  | NULL
-- 5  | 식품

SELECT COUNT(*)                AS total_rows,      -- 5 (NULL 행 포함)
       COUNT(category)         AS non_null_count,   -- 4 (NULL 제외)
       COUNT(DISTINCT category) AS distinct_count;   -- 2 (전자, 식품)

-- 점수 데이터: 80, 90, NULL, 70, NULL

SELECT AVG(score) AS avg_score FROM students;
-- 결과: (80 + 90 + 70) / 3 = 80
-- NULL인 2명은 분모에서도 제외됨!

-- NULL을 0으로 포함하여 계산하고 싶으면:
SELECT AVG(COALESCE(score, 0)) AS avg_score FROM students;
-- 결과: (80 + 90 + 0 + 70 + 0) / 5 = 48

-- Oracle에서는 NVL 사용
SELECT AVG(NVL(score, 0)) AS avg_score FROM students;

-- 카테고리별 통계
SELECT category,
       COUNT(*) AS 상품수,
       AVG(price) AS 평균가격,
       SUM(stock) AS 총재고
FROM products
GROUP BY category;

-- ✓ category가 GROUP BY에 있으므로 SELECT에 사용 가능
SELECT category, AVG(price)
FROM products
GROUP BY category;

-- ✓ 여러 컬럼으로 그룹화
SELECT category, brand, AVG(price)
FROM products
GROUP BY category, brand;

-- 연도별, 카테고리별 매출 통계
SELECT YEAR(order_date) AS 연도,
       p.category AS 카테고리,
       SUM(oi.quantity * oi.unit_price) AS 매출
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY YEAR(order_date), p.category
ORDER BY 연도, 매출 DESC;

연도  | 카테고리 | 매출
------|---------|----------
2023  | 전자    | 12000000
2023  | 식품    | 3000000
2024  | 전자    | 15000000
2024  | 식품    | 4500000

SELECT category, AVG(price) AS avg_price
FROM products
WHERE stock > 0              -- 먼저: 재고 있는 상품만 (행 필터링)
GROUP BY category
HAVING AVG(price) > 50000;   -- 이후: 평균 가격 5만 이상인 그룹만 (그룹 필터링)

-- 상품이 2개 이상인 카테고리만
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

-- 총 매출 100만 이상이고 주문 수 10건 이상인 회원
SELECT u.username,
       SUM(o.total_amount) AS total,
       COUNT(o.order_id) AS orders
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.username
HAVING SUM(o.total_amount) >= 1000000
   AND COUNT(o.order_id) >= 10;

-- ✗ 비효율: 모든 행을 그룹화한 후 카테고리 필터링
SELECT category, AVG(price)
FROM products
GROUP BY category
HAVING category = '전자';

-- ✓ 효율: 먼저 행을 줄인 후 그룹화
SELECT category, AVG(price)
FROM products
WHERE category = '전자'
GROUP BY category;

-- 매출이 높은 카테고리 순서로 정렬
SELECT category, SUM(price * stock) AS estimated_value
FROM products
GROUP BY category
ORDER BY estimated_value DESC;

-- category가 NULL인 상품도 하나의 그룹으로
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category;

category | cnt
---------|----
전자     | 2
식품     | 3
NULL     | 1    ← NULL도 하나의 그룹

SELECT category, brand, SUM(price) AS total_price
FROM products
GROUP BY ROLLUP(category, brand);

SELECT category, brand, SUM(price) AS total_price
FROM products
GROUP BY CUBE(category, brand);

SELECT category, brand, SUM(price) AS total_price
FROM products
GROUP BY GROUPING SETS (
    (category, brand),   -- 카테고리+브랜드별
    (category),          -- 카테고리별
    ()                   -- 전체
);

SELECT CASE WHEN GROUPING(category) = 1 THEN '전체'
            ELSE COALESCE(category, '미분류')
       END AS category,
       SUM(price) AS total_price
FROM products
GROUP BY ROLLUP(category);

-- 주문한 적 있는 고유 회원 수
SELECT COUNT(DISTINCT user_id) AS unique_customers
FROM orders;

-- 카테고리별 고유 브랜드 수
SELECT category, COUNT(DISTINCT brand) AS brand_count
FROM products
GROUP BY category;

-- 상태별 주문 수를 한 행으로 피벗
SELECT COUNT(CASE WHEN status = 'PAID' THEN 1 END) AS paid,
       COUNT(CASE WHEN status = 'SHIPPED' THEN 1 END) AS shipped,
       COUNT(CASE WHEN status = 'DELIVERED' THEN 1 END) AS delivered
FROM orders;

paid | shipped | delivered
-----|---------|----------
15   | 8       | 42

-- 카테고리별 매출 비율
SELECT category,
       SUM(price * stock) AS value,
       ROUND(SUM(price * stock) * 100.0 /
             (SELECT SUM(price * stock) FROM products), 1) AS pct
FROM products
GROUP BY category
ORDER BY pct DESC;

SELECT category, SUM(total_amount) AS revenue
FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
GROUP BY category
ORDER BY revenue DESC
LIMIT 3;  -- MySQL, PostgreSQL
-- SQL Server: TOP 3, Oracle: ROWNUM <= 3 또는 FETCH FIRST 3 ROWS ONLY

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

-- Oracle
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

SELECT CASE
         WHEN price < 10000 THEN '1만원 미만'
         WHEN price < 50000 THEN '1~5만원'
         WHEN price < 100000 THEN '5~10만원'
         ELSE '10만원 이상'
       END AS price_range,
       COUNT(*) AS product_count
FROM products
GROUP BY CASE
         WHEN price < 10000 THEN '1만원 미만'
         WHEN price < 50000 THEN '1~5만원'
         WHEN price < 100000 THEN '5~10만원'
         ELSE '10만원 이상'
       END
ORDER BY MIN(price);