3-35 列数固定の集計

SQLパズル

StoreTable    SellTable
StoreName     Book   StoreName
---------     -----  ---------
StoreA        BookA  StoreA
StoreB        BookA  StoreA
StoreC        BookC  StoreC
StoreD        BookD  StoreA
StoreE        BookD  StoreA
              BookD  StoreB

StoreTableのStoreNameごとの販売数と
全てのStoreの販売数の合計を
出力する。

SellTableの、
Book列は、BookA,BookB,BookC,BookDのいずれかとする。

出力結果
StoreName   BookA  BookB  BookC  BookD  Total
----------  -----  -----  -----  -----  -----
StoreA          2      0      0      2      4
StoreB          0      0      0      1      1
StoreC          0      0      1      0      1
StoreD          0      0      0      0      0
StoreE          0      0      0      0      0
AllStore        2      0      1      3      6

データ作成スクリプト

create table StoreTable(StoreName) as
select 'StoreA' from dual union
select 'StoreB' from dual union
select 'StoreC' from dual union
select 'StoreD' from dual union
select 'StoreE' from dual;

create table SellTable(Book check(Book in('BookA','BookB','BookC','BookD')),StoreName) as
select 'BookA','StoreA' from dual union all
select 'BookA','StoreA' from dual union all
select 'BookC','StoreC' from dual union all
select 'BookD','StoreA' from dual union all
select 'BookD','StoreA' from dual union all
select 'BookD','StoreB' from dual;

SQL

col StoreName for a10

select case when grouping(a.StoreName) = 1
            then 'AllStore' else a.StoreName end as StoreName,
count(decode(b.Book,'BookA',1)) as BookA,
count(decode(b.Book,'BookB',1)) as BookB,
count(decode(b.Book,'BookC',1)) as BookC,
count(decode(b.Book,'BookD',1)) as BookD,
count(b.Book) as Total
  from StoreTable a,SellTable b
 where a.StoreName = b.StoreName(+)
group by RollUp(a.StoreName)
order by grouping(a.StoreName),a.StoreName;

解説

Pivotの、
行が可変で、列が固定のパターンの応用と言えるでしょう。

---