オーロラさんの勉強帳

33歳、未経験からIT業界へ。SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【SQL】【基礎】GROUP BY、SUMを使って年月で集計する

以下のような商品の納入日を管理しているテーブルで、年月ごとの仕入価格を集計するSQLを考えたいと思います。

f:id:auroralights:20200607002631p:plain

 

<目次>

 

データの準備

今回使ったデータは以下のSQLで作成できます。

 

<テーブルの作成>

CREATE TABLE product_history (
product_id VARCHAR2(6) constraint product_id_nn NOT NULL,
product_name VARCHAR2(15) constraint product_name_nn NOT NULL,
price NUMBER(6),
p_class CHAR(10),
delivery_date Date
);

 

<データの挿入>

insert into product_history VALUES('A111','キャベツ',100,'vegetables','2019/12/01');
insert into product_history VALUES('A122','レタス',200,'vegetables','2019/12/10');
insert into product_history VALUES('A1234','白菜',120,'vegetables','2019/12/20');
insert into product_history VALUES('A55555','茗荷',130,'vegetables','2020/01/01');
insert into product_history VALUES('B123','イチゴ',390,'fruit','2020/01/15');
insert into product_history VALUES('B555','桃',450,'fruit','2020/01/20');
insert into product_history VALUES('A111','キャベツ',100,'vegetables','2020/02/02');
insert into product_history VALUES('A122','レタス',200,'vegetables','2020/02/10');
insert into product_history VALUES('A1234','白菜',120,'vegetables','2020/02/14');
insert into product_history VALUES('A55555','茗荷',130,'vegetables','2020/02/20');
insert into product_history VALUES('B123','イチゴ',390,'fruit','2020/03/05');
insert into product_history VALUES('B555','桃',450,'fruit','2020/03/10');
insert into product_history VALUES('A111','キャベツ',100,'vegetables','2020/03/15');
insert into product_history VALUES('A122','レタス',200,'vegetables','2020/03/20');
insert into product_history VALUES('A1234','白菜',120,'vegetables','2020/04/01');
insert into product_history VALUES('A55555','茗荷',130,'vegetables','2020/04/05');
insert into product_history VALUES('B123','イチゴ',390,'fruit','2020/04/10');
insert into product_history VALUES('B555','桃',450,'fruit','2020/04/15');

 

年月ごとのP_CLASSの仕入価格合計を抽出

抽出するテーブルはproduct_historyなので、

from product_history

 

年月ごとの仕入価格合計、かつP_CLASSごとなので、年月、P_CLASSをGROUP BYします。
年月についてはto_charでyyyy/mmとしてGROUP BYします。

from product_history
group by to_char(a.delivery_date,'yyyy/mm'),a.p_class

 

次に抽出するカラムを指定します。

select to_char(a.delivery_date,'yyyy/mm') 年月,
a.p_class ,
sum(a.price)

from product_history

group by to_char(a.delivery_date,'yyyy/mm'),a.p_class

 

最後にorder byで年月順にします。

select to_char(a.delivery_date,'yyyy/mm') 年月,
a.p_class ,
sum(a.price)
from product_history a
group by to_char(a.delivery_date,'yyyy/mm'),a.p_class
order by to_char(a.delivery_date,'yyyy/mm');

※表別名をaとしてカラム前に付けています。

 

年月ごとのキャベツの仕入価格合計を抽出する

キャベツなので、whereで条件をキャベツにしました。
年月・P_CLASSごとと違って、GROUP BYおよび抽出するカラムを変更しています。

select to_char(a.delivery_date,'yyyy/mm') 年月,
a.product_id,
a.PRODUCT_NAME,
sum(a.price)
from product_history a
where a.product_name = 'キャベツ'
group by to_char(a.delivery_date,'yyyy/mm'),a.PRODUCT_ID,a.PRODUCT_NAME
order by to_char(a.delivery_date,'yyyy/mm');