SQLでグループ毎の集計を列として追加するSELECT文を書きたい

Question

前提・実現したいこと

製品の入出庫を管理するstock_historyテーブルがあります。

CREATE TABLE "stock_history" (
	"id"	INTEGER,
	"date"	TEXT,
	"product_id"	TEXT,
	"type"	TEXT,
	"count"	TEXT,
	PRIMARY KEY("id")
)

dateは入出庫した日付
product_idは入出庫した製品のID
typeは入庫か出庫かを表す数字（"0"=入庫、"1"=出庫）
countは入出庫した製品の数です。

このテーブルに

id	date	product_id	type	count
1	2020-09-25	1001	0	50
2	2020-09-25	1001	1	40
3	2020-09-26	1001	0	30
4	2020-09-26	1001	1	20

のようにレコードがあったとして、
これをdateとproduct_idでグルーピングし、
そのグループ単位での入庫と出庫別のcountの合計を新たに列に加え、

date	product_id	in_count	out_count
2020-09-25	1001	50	40
2020-09-26	1001	30	20

のように取得するにはどのようなSQLを書けばよいでしょうか？
ご教授いただけると幸いです

発生している問題・エラーメッセージ

「該当のソースコード」で示したSQLを実行すると

date	product_id	in_count	out_count
2020-09-25	1001	0	40
2020-09-26	1001	0	20

が返され、in_countのみ合計が算出されません。

該当のソースコード

SELECT date,product_id,in_count,out_count
FROM(
SELECT date,product_id,SUM(count)AS in_count,0 AS out_count FROM stock_history WHERE type = 0 GROUP BY date,product_id
UNION 
SELECT date,product_id,0 AS in_count,SUM(count)AS out_count FROM stock_history WHERE type = 1 GROUP BY date,product_id
)
GROUP BY date,product_id

補足情報（FW/ツールのバージョンなど）

SQLite3

Answer 1

集計する対象をcase句によって分岐させる事によって、場合別けした集計ができます。

SELECT  date, product_id
      , SUM(case when type = 0 then count else 0 end) as in_count
      , SUM(case when type = 1 then count else 0 end) as out_count
FROM  stock_history
GROUP BY  date, product_id

Comments

[h2thfu]

できました。
ありがとうございます。

Answer 2

$ cat hoge.sql

CREATE TABLE "stock_history" (
    "id" INTEGER,
    "date" TEXT,
    "product_id" TEXT,
    "type" TEXT,
    "count" TEXT,
    PRIMARY KEY("id")
);

insert into stock_history
values ('1', '2020-09-25', '1001', '0', '50');
insert into stock_history
values ('2', '2020-09-25', '1001', '1', '40');
insert into stock_history
values ('3', '2020-09-26', '1001', '0', '30');
insert into stock_history
values ('4', '2020-09-26', '1001', '1', '20');

with a as (
    select date,
        product_id,
        sum(count) as in_count
    from stock_history
    where type = 0
    group by date,
        product_id
),
b as (
    select date,
        product_id,
        sum(count) as out_count
    from stock_history
    where type = 1
    group by date,
        product_id
)
select a.date,
    a.product_id,
    a.in_count,
    b.out_count
from a
    join b
where a.date = b.date
    and a.product_id = b.product_id;

$ sqlite3 < hoge.sql 
2020-09-25|1001|50|40
2020-09-26|1001|30|20

Comments

[h2thfu]

できました。
ありがとうございます。
with句でサブクエリを変数のように保持できるのですね。
参考になります。

Answer 3

よりスマートな回答を既に頂いておりますが、
私のソースコードでも以下の変更をすることで望む動作をしました。

-SELECT date,product_id,in_count,out_count
+SELECT date,product_id,SUM(in_count),SUM(out_count)
FROM(
SELECT date,product_id,SUM(count)AS in_count,0 AS out_count FROM stock_history WHERE type = 0 GROUP BY date,product_id
UNION 
SELECT date,product_id,0 AS in_count,SUM(count)AS out_count FROM stock_history WHERE type = 1 GROUP BY date,product_id
)
GROUP BY date,product_id