7-66 プロジェクトごとに集計

SQLパズル

Projectsテーブル
projID  name
------  --------
    40  Graphics
    50  Software

Chargesテーブル
empID  projID  isBillable  totalHours
-----  ------  ----------  ----------
   10      40  T                    4
   10      50  F                    1
   10      50  T                    5
   12      40  T                    2
   12      50  F                    3

プロジェクトごとの集計を出力する。

出力結果
Project   Non-Billable Hours  Billable Hours
--------  ------------------  ---------------
Graphics                   6                0
Software                   5                4

データ作成スクリプト

create table Projects as
select 40 as projID,'Graphics' as name from dual
union select 50,'Software' from dual;

create table Charges as
select 10 as empID,40 as projID,'T' as isBillable,4 as totalHours from dual
union select 12,40,'T',2 from dual
union select 12,50,'F',3 from dual
union select 10,50,'F',1 from dual
union select 10,50,'T',5 from dual;

SQL

--■■■相関サブクエリを使う方法■■■
select distinct
(select b.name from Projects b
  where b.projID = a.projID) as Project,
nvl(sum(decode(isBillable,'T',totalHours)) over(partition by projID),0) as "Non-Billable Hours",
nvl(sum(decode(isBillable,'F',totalHours)) over(partition by projID),0) as "Billable Hours"
from Charges a
order by Project;

--■■■内部結合を使う方法■■■
select b.name,
sum(decode(isBillable,'T',totalHours,0)) as "Non-Billable Hours",
sum(decode(isBillable,'F',totalHours,0)) as "Billable Hours"
  from Charges a,Projects b
 where a.projID=b.projID
group by a.projID,b.name;

解説

nvl関数とsum関数を組み合わせてます。

---