SQLにてIDごとのカウント結果をまとめて一つの表にする

Question

お世話になっております。
よろしくお願いします。

やりたいこと

下記①②のような表があったとき、IDごとに特定のNumberのカウントを取得するビュー③を作成したいです。
※①のテーブルではNumber=1～3, ②のテーブルではNumber=12だけカウント
①元テーブル1

ID	Number1
田中	1
高橋	2
高橋	4
斉藤	1
斉藤	3
斉藤	3
斉藤	12

②元テーブル2

ID	Number2
田中	3
林	2
林	12
林	12

③作成したいビュー

ID	countOf_1	countOf_2	countOf_3	countOf_12
田中	1	0	0	0
高橋	0	1	0	0
斉藤	1	0	2	0
林	0	0	0	2

試したこと

IDごとのカウントは下記二つの方法で試してみて、取得できました。

※方法1
SELECT DISTINCT t1.ID, COUNT(*) AS countOf_1
FROM 元テーブル1 t1
WHERE t1.Number = 1
GROUP BY t1.ID;
※別Numberや元テーブル2の取得はFROM句とWHERE句を変更するだけなので省略します

※方法2
①元テーブル1
SELECT DISTINCT t1.ID,
       COUNT(CASE WHEN t1.Number = 1 THEN 'X' ELSE NULL END) AS countOf_1,
       COUNT(CASE WHEN t1.Number = 2 THEN 'X' ELSE NULL END) AS countOf_2,
FROM 元テーブル1 t1
GROUP BY t1.ID;

②元テーブル2
SELECT DISTINCT t2.ID,
       COUNT(CASE WHEN t2.Number = 3 THEN 'X' ELSE NULL END) AS countOf_3
FROM 元テーブル2 t2
GROUP BY t2.ID;

方法1だと、それぞれで取得した表を一つにまとめることができませんでした。
調べた結果、外部結合で実施できるのだと思いましたが、どう書けばいいかわかりませんでした。

また方法2だと、テーブル1から取得した結果は一つの表にまとめられましたが、テーブル2から取得した結果を結合することができませんでした。

ご教授のほど、よろしくお願いします。
追記

正しい例を提示していませんでした。申し訳ございません。
元テーブル1と元テーブル2のカラム"Number"は実際には違うカラム名となります。
またyambejp様、sazi様のご回答を参考に実行すると結合された表は作成されるのですが、countof_1～4のsum結果が実際と異なってしまいました。
コードを記載します（※また実際と異なるといけないため、条件など実際の環境に合わせたコードを載せています）

select coalesce(tbl1.ID, tbl2.ID) as ID
     , sum(case tbl1.Number1 when 1 then 1 else 0 end) as countOf_1
     , sum(case tbl1.Number1 when 2 then 1 else 0 end) as countOf_2
     , sum(case tbl1.Number1 when 3 then 1 else 0 end) as countOf_3
     , sum(case tbl2.Number2 when 12 then 1 else 0 end) as countOf_12
from TABLE1 tbl1 full join TABLE2 tbl2
     on   tbl1.ID = tbl2.ID
      and tbl1.Number1 in (1,2,3)
      and tbl2.Number2 = 12
group by coalesce(tbl1.ID, tbl2.ID)
order by coalesce(tbl1.ID, tbl2.ID);

Answer 1

union か full join ですね。
union はyambejpさんが回答されているので、full joinを。
データはyambejpさんのをお借りしてます。

select coalesce(tbl1.id, tbl2.id) as id
     , sum(case tbl1.Number when 1 then 1 else 0 end) as countOf_1
     , sum(case tbl1.Number when 2 then 1 else 0 end) as countOf_2
     , sum(case tbl2.Number when 3 then 1 else 0 end) as countOf_3
from tbl1 full join tbl2
     on   tbl1.id = tbl2.id 
      and tbl1.number in (1,2)
      and tbl2.number = 3
group by coalesce(tbl1.id, tbl2.id)
order by coalesce(tbl1.id, tbl2.id)

Comments

[inu_inu]

申し訳ございませんが提示させていただいた例に誤りがありました。
また誤りを修正してsazi様のご回答いただいた内容を実施してみましたが、カウント数が実際と異なるようでした。(例と実際が異なることが原因と思います)
正しい内容や別途試した内容を追記しています。よろしければ、ご確認のほどお願いします。

[sazi]

質問のテーブルと得たい結果のデータサンプルも変更して下さい。

[inu_inu]

失礼いたしました。テーブルについても修正させていただきました。もともと記載していた表部分を修正しています。

[sazi]

質問に追加されたSQLの結果については、作成したいビューとIDの並びは違いますが、結果はおかしくないのですが？

[inu_inu]

countOf_12が0ではない場合、値が崩れるようです…
countOf_12が0であると正しく表示されます。
sazi様の環境で正しく表示されるのであれば、どこかで記載を間違えているのかもしれませんね、確認してみます。

[sazi]

>, sum(case tbl2.Number2 when 12 then 1 else 0 end) as countOf_12
思いつくのは、上記の部分の実際の記述で、参照しているテーブルとカラム名が誤っている位ですかね。

[inu_inu]

見直し・書き直ししてみましたが、やはり合いませんでした…
また、改めて結果を見るとcoutof_1～3,12のすべてが0になっているレコードがありました。
countOf_12以外でも書き誤っている可能性もあるため、今一度確認・調査をしてみます。

[sazi]

この質問内にあるSQLに不備があるなら、確認はしていただきたいと思いますが、table1とtable2の内容を比較するような事がないならunionの方が良いと思いますので、そこまで追跡されなくても大丈夫です。

[inu_inu]

比較することはないです。比較せず単純に合わせて表示するならUNIONの方がより適切ということでしょうか。
別の方法もご教授していただきありがとうございます。非常に勉強になりました。

[sazi]

先ずはunionの方が記述がシンプルだという事。
速度的にもunionの方が早そうな気がしますが、それは実行計画で確認されて下さい。

Answer 2

一つもないのはNULLじゃないくて0でよいのでは？

• 元データ

create table tbl1(ID varchar(10),Number int);
insert into tbl1 values
('田中',1),
('高橋',2),
('斉藤',1),
('斉藤',2),
('斉藤',2),
('斉藤',3);

create table tbl2(ID varchar(10),Number int);
insert into tbl2 values
('田中',3),
('林',2),
('林',3),
('林',3);

• 集計

select ID
,sum(case Number when 1 then 1 else 0 end) as countOf_1
,sum(case Number when 2 then 1 else 0 end) as countOf_2
,sum(case Number when 3 then 1 else 0 end) as countOf_3
from(
select * from tbl1 where Number in (1,2)
union all select * from tbl2 where Number =3
) as t1
group by ID

追記

UNIONでちがうカラム名のものを結合する場合、1行目のselectのカラム名が優先されます。
必要に応じて適当なエイリアスを振ってあげるとわかりやすくなるでしょう

create table tbl1(ID varchar(10),Number1 int);
insert into tbl1 values
('田中',1),
('高橋',2),
('高橋',4),
('斉藤',1),
('斉藤',3),
('斉藤',3),
('斉藤',12);

create table tbl2(ID varchar(10),Number2 int);
insert into tbl2 values
('田中',3),
('林',2),
('林',12),
('林',12);


select ID
,sum(case Number when 1 then 1 else 0 end) as countOf_1
,sum(case Number when 2 then 1 else 0 end) as countOf_2
,sum(case Number when 3 then 1 else 0 end) as countOf_3
,sum(case Number when 12 then 1 else 0 end) as countOf_4
from(
select ID,Number1 as Number from tbl1 where Number1 in (1,2,3)
union all select ID,Number2 from tbl2 where Number2 =12
) as t1
group by ID;

Comments

[inu_inu]

条件に一致する場合は1、一致しない場合は0として、和を出すことでカウントするのですね！
なるほど、とても参考になります、ありがとうございます。
また、申し訳ございませんが提示させていただいた例に誤りがありました。
ご回答いただいた内容だとうまく実行できず、例の誤りに気付きました。申し訳ございません…
正しい内容や別途試した内容を追記しています。よろしければ、ご確認のほどお願いします。

[yambejp]

tbl1はNumber→Number1で1,2,3、
tbl2はNumber→Number2で12
を抜きだすのですね？
できれば適当なサンプルを質問に追記してもらえると
それにあわせた回答ができると思います

[inu_inu]

ご認識の通りです。
テーブルについても修正させていただきました。もともと記載していた表部分を修正しています。

[yambejp]

追記しておきました

[inu_inu]

ありがとうございます、実現したいデータが出力されました。
UNIONについても、sazi様・Orlofsky様のJOINについても、私の認識していた動きが誤っていたことに気づくことができました。
でUNIONしたカラムについても、UNION前のエイリアスが反映されることも知らなかったため、非常に助かりました。

Answer 3

また方法2だと、テーブル1から取得した結果は一つの表にまとめられましたが、テーブル2から取得した結果を結合することができませんでした。

ヒントはJOIN。ひとつのSELECTで複数のテーブルから情報を取得できます。

なお、養子や結婚で苗字が変わった時の対応もできるようにきちんとテーブル設計するのもモラルです。

Comments

[inu_inu]

JOINの考え方について、「テーブルから取得した複数の表を作って、複数の表を結合していく」と考えていましたが、「テーブルから取得して一つの大きな表を作り、そこから絞り込む条件によって再度表を成形する」ような考えなのですね。(表現下手ですが)
もう少し考えてみたいと思います。
また名字に関して、今回例として上記二つのカラムと値を用いましたが、実際はIDに対して別カラムで名字に該当する値を持たせているため、結婚時なども対応できるようにしています。ありがとうございます。