MSSQL - 그룹핑 후 집계하는 방법

MSSQL

MSSQL에서는  WITH ROLLUP을 사용한다.

SELECT country, product, sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

ROLLUP 적용 전 (왼쪽) VS ROLLUP을 적용한 모습(오른쪽)

countryproductsum(profit) countryproductsum(profit)

Finland	Computer	1500		Finland	Computer	1500
Finland	Phone	110		Finland	Phone	110
India	Calculator	150		Finland	NULL	1610
India	Computer	1200		India	Calculator	150
USA	Calculator	125		India	Computer	1200
USA	Computer	4200		India	NULL	1350
USA	TV	250		USA	Calculator	125
				USA	Computer	4200
				USA	TV	250
				USA	NULL	4575
				NULL	NULL	7535

ROLLUP은 집계한 기준값을 NULL값으로 대체한다. COALESCE을 활용하면 원하는 텍스트를 넣을 수 있다.

( MySQL에서는 IFNULL로 대체 가능)

SELECT COALESCE(country,"ALL countries") as country, 
       COALESCE(product,"ALL products") as product, 
       sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

countryproductsum(profit)

Finland	Computer	1500
Finland	Phone	110
Finland	ALL products	1610
India	Calculator	150
India	Computer	1200
India	ALL products	1350
USA	Calculator	125
USA	Computer	4200
USA	TV	250
USA	ALL products	4575
ALL countries	ALL products	7535