/***************************************************************************************
-- Title : [2k] ROLLUP과 CUBE 사용 예
-- Reference : dbrang.tistory.com
-- Key word : rollup, cube
***************************************************************************************/
use tempdb
drop table inventory
create table inventory
( item varchar(10)
, color varchar(5)
, quantity tinyint
)
insert into inventory values('Table', 'Blue', '124')
insert into inventory values('Table', 'Red', '223')
insert into inventory values('Chair', 'Blue', '101')
insert into inventory values('Chair', 'Red', '210')
-- 테이블 조회
select * from inventory
-- CUBE 셈플
select item, color, sum(quantity) "quantity"
from inventory
group by item, color with cube
select case when (grouping(item) = 1) then 'ALL'
else isnull(item, 'UNKNOWN')
end as item,
case when (grouping(color) = 1) then 'ALL'
else isnull(color, 'UNKNOWN')
end as color,
sum(quantity) as qtysum
from inventory
group by item, color with cube
select case when (grouping(Item) = 1) then 'ALL'
else isnull(Item, 'UNKNOWN')
end as Item,
sum(quantity) as qtysum
from inventory
group by item with cube
-- ROLLUP 셈플
select item, color, sum(quantity) "quantity"
from inventory
group by item, color with rollup
select case when (grouping(item) = 1) then 'ALL'
else isnull(item, 'UNKNOWN')
end as item,
case when (grouping(color) = 1) then 'ALL'
else isnull(color, 'UNKNOWN')
end as color,
sum(quantity) as qtysum
from inventory
group by item, color with rollup
select case when (grouping(Item) = 1) then 'ALL'
else isnull(Item, 'UNKNOWN')
end as Item,
sum(quantity) as qtysum
from inventory
group by item with rollup