[SQL] ROLLUP을 사용한 카테고리별 합계

0. 목표

 - ROLLUP 을 사용한 카테고리별 합계 구하기

1. 실습

 1) 테이블 생성

CREATE TABLE pbj_db.rollup_test
(
    year INT NOT NULL,
    country VARCHAR(32) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit INT NOT NULL
) ENGINE = INNODB;

 2) 데이터 삽입

INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'Finland', 'Computer', 1500);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'Finland', 'Phone', 100);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'Finland', 'Phone', 10);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'India', 'Calculator', 75);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'India', 'Computer', 1200);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'India', 'Calculator', 75);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'USA', 'Calculator', 75);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2000, 'USA', 'Computer', 1500);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'USA', 'Calculator', 50);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'USA', 'Computer', 1200);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'USA', 'TV', 100);
INSERT INTO pbj_db.rollup_test(year, country, product, profit) VALUES(2001, 'USA', 'TV', 150);

 3) 데이터 확인

SELECT * FROM pbj_db.rollup_test;

  - 결과

 4) ROLLUP을 통합 합계

SELECT 
	country, 
    product, 
    SUM(profit) 
FROM pbj_db.rollup_test 
GROUP BY country, product WITH ROLLUP;

  - 결과

 5) COALESCE을 통한 NULL 값 채우기

SELECT 
	COALESCE(country,"ALL countries") as country, 
	COALESCE(product,"ALL products") as product, 
	SUM(profit) 
FROM pbj_db.rollup_test 
GROUP BY country, product WITH ROLLUP;

  - 결과

2. 참고

 - SQL에서 소계, 합계를 계산하는 ROLLUP 활용하기