group by句とhaving句の使い方

①主な集計関数

・SUM
・AVG
・MAX
・MIN
・COUNT

②それぞれの句は、以下の順にどちらもOK

・select句、from句、where句、group by句、having句、order by句
・select句、from句、where句、having句、group by句、order by句

③group by句とhaving句の使用例

-- テーブル作成と必要なデータの挿入
create table t1 (id number(4), name char(10), dt date, sal number(10),yobi char(2));
insert into t1 values(1,'A1',to_date('2025/01/01','yyyy/mm/dd'),100,1);
insert into t1 values(1,'A2',to_date('2025/02/01','yyyy/mm/dd'),100,2);
insert into t1 values(2,'A3',to_date('2025/03/01','yyyy/mm/dd'),NULL,3);
insert into t1 values(2,'A4',to_date('2025/04/01','yyyy/mm/dd'),200,4);
insert into t1 values(2,'A5',to_date('2025/05/01','yyyy/mm/dd'),400,5);
insert into t1 values(3,'A6',to_date('2025/06/01','yyyy/mm/dd'),100,6);

commit;

-- group byなしデータ集計処理
select max(name),min(dt),sum(sal),count(1) from t1;

-- group byありデータ集計処理
select max(name),min(dt),sum(sal),count(1) from t1 group by id;

-- where：集計前にデータ抽出
select max(name),min(dt),sum(sal),count(1) from t1 where dt <'2025/06/01' group by id;

-- having：集計後にデータ絞る
select max(name),min(dt),sum(sal),count(1) from t1 where dt <'2025/06/01' group by id having avg(sal) > 100;

-- having：集計列または集計関数
select max(name),min(dt),sum(sal),count(1) from t1 where dt <'2025/06/01' group by id having id > 0;

-- order byでデータソート
select max(name),min(dt),sum(sal),count(1) from t1 where dt <'2025/06/01' group by id having id > 0 order by id desc;

Follow me!