路線ごとに物件数を集計したい

Question

データベースを学習中で不動産物件数を集計するSQLがわかりません。
グループ化したりと試してみましたが、、、複数の路線がある場合、1つのSQLで集計できるのでしょうか？

路線データ

路線ID	路線名
1	路線A
2	路線B
3	路線C
4	路線D

物件データ（3つまで路線IDが保存されています。）
|物件ID|物件名|路線1|路線2|路線3|
|:--|:--:|--:|
|1|物件A|1|2|3|
|2|物件B|1|2||
|3|物件C|2|||

を路線ごとに集計したい

路線ID	路線名	物件数
1	路線A	2
2	路線B	3
3	路線C	1
4	路線D	0

路線が一つだけだと集計も簡単にできたのですが、3つあると？？？です。

Answer 1

相関副問合せでCOUNTします。

select  路線ID, 路線名
     , coalesce((select count(物件ID) from 物件 
        where 路線.路線ID in (路線1, 路線2, 路線3)
       ), 0) as 物件数
from  路線

結合しての集計なら

select  路線ID, 路線名, count(物件ID)
from  路線 left join 物件
      on    路線.路線ID in (物件.路線1, 物件.路線2, 物件.路線3)
group by 路線ID, 路線名

Comments

[takumil]

回答いただきありがとうございました。

Answer 2

路線-物件テーブルを別に持つか、路線データをJSON型にするかで
管理が楽になると思います

Comments

[takumil]

回答いただきありがとうございました。

Answer 3

こんな感じでしょうか。

テーブル定義

create table rosen (
  rosen_id int primary key,
  rosen_name varchar(255)
);

create table bukken (
  bukken_id int primary key,
  bukken_name varchar(255),
  rosen1 int references rosen (id),
  rosen2 int references rosen (id),
  rosen3 int references rosen (id)
);

insert into rosen values (1, '路線A');
insert into rosen values (2, '路線B');
insert into rosen values (3, '路線C');
insert into rosen values (4, '路線D');

insert into bukken values (1, '物件A', 1, 2, 3);
insert into bukken values (2, '物件B', 1, 2, null);
insert into bukken values (3, '物件C', 2, null, null);

問い合わせ

select rosen_id, rosen_name, count(bukken_id) as bukken_count
  from (
    select rosen_id, rosen_name, bukken_id
      from rosen left outer join bukken on rosen_id = bukken.rosen1
    union
    select rosen_id, rosen_name, bukken_id
      from rosen left outer join bukken on rosen_id = bukken.rosen2
    union
    select rosen_id, rosen_name, bukken_id
      from rosen left outer join bukken on rosen_id = bukken.rosen3
  ) t
  group by rosen_id, rosen_name;

結果

+----------+------------+--------------+
| rosen_id | rosen_name | bukken_count |
+----------+------------+--------------+
|        1 | 路線A      |            2 |
|        2 | 路線B      |            3 |
|        3 | 路線C      |            1 |
|        4 | 路線D      |            0 |
+----------+------------+--------------+

Comments

[takumil]

回答いただきありがとうございました。