レコードがnullを0（ゼロ）として表示させたい

Question

実現したいこと

• sqlでレコードがないデータを「0（ゼロ）」と、表示させたい。
• 表側となる「department_id」を、「総務」「経理」「庶務」などといった、部署名を表示させたい。（画像中にある、緑矢印部分は、「department_id」のid（5、8）が来るはずが、レコードがないデータであるため、表示されない。）

前提

PHPとmysqlでクロス集計画面を作っています。

該当のソースコード

select
department_id,
/*date_format(dateOccurre1,'%Y%m') as yymm,*/
COUNT(*) as 合計,
sum(case when level_id <= 4 then 1 else 0 end) as Ｉ計,
sum(case when level_id >= 5 then 1 else 0 end) as Ａ計,
sum(case level_id when 1 then 1 else 0 end) as ０,
sum(case level_id when 2 then 1 else 0 end) as １,
sum(case level_id when 3 then 1 else 0 end) as ２,
sum(case level_id when 4 then 1 else 0 end) as ３ａ,
sum(case level_id when 5 then 1 else 0 end) as ３ｂ,
sum(case level_id when 6 then 1 else 0 end) as ４,
sum(case level_id when 7 then 1 else 0 end) as ５
FROM t_general
LEFT OUTER JOIN department ON (t_general.department_id = department.id)
GROUP BY department_id
ORDER BY department_id ASC

-- テーブルの構造 `t_general`
--

CREATE TABLE `t_general` (
  `id` int(11) NOT NULL,
  `time_create` datetime NOT NULL,
  `department_id` int(11) NOT NULL,
  `occupation_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `yearsExp` int(11) NOT NULL,
  `level_id` int(11) NOT NULL,
  `dateOccurre1` date NOT NULL,
  `date_weekday_id` int(11) NOT NULL,
  `date_ampmwork_id` int(11) NOT NULL,
  `place_id` int(11) NOT NULL,
  `kind_id` int(11) NOT NULL,
  `textContent` varchar(500) NOT NULL,
  `textResponse` varchar(500) NOT NULL,
  `cause_human_id` int(11) NOT NULL,
  `cause_material_id` int(11) NOT NULL,
  `cause_system_id` int(11) NOT NULL,
  `cause_control_id` int(11) NOT NULL,
  `cause_others_id` int(11) NOT NULL,
  `textMeasure` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- テーブルのインデックス `t_general`
--
ALTER TABLE `t_general`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `occupation_id` (`occupation_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `level_id` (`level_id`),
  ADD KEY `date_weekday_id` (`date_weekday_id`),
  ADD KEY `date_ampmwork_id` (`date_ampmwork_id`),
  ADD KEY `place_id` (`place_id`),
  ADD KEY `kind_id` (`kind_id`),
  ADD KEY `cause_human_id` (`cause_human_id`),
  ADD KEY `cause_material_id` (`cause_material_id`),
  ADD KEY `cause_system_id` (`cause_system_id`),
  ADD KEY `cause_control_id` (`cause_control_id`),
  ADD KEY `cause_others_id` (`cause_others_id`);

INSERT INTO `department` (`id`, `depa`) VALUES
(1, 'ーーーー'),
(2, '総務'),
(3, '経理'),
(4, '庶務'),
(5, '人事部'),
(6, '法務部'),
(7, '情報システム部'),
(8, '技術部'),
(9, 'マーケティング部'),
(10, '営業部'),
(11, 'CS部'),
(12, '相談室'),
(13, '一般'),
(14, '保育室'),
(15, 'その他');

試したこと

「LEFT OUTER JOIN」を使用してみましたが、レコードがない部署は、表示されませんでした。

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

ここにより詳細な情報を記載してください。

Comments

[yambejp]

サンプルデータが不十分で状況が確認できません

[m.ts10806]

そもそもの定義をNULL不可にして初期値0にするのはどうでしょう。
要件次第かもしれませんが、その要件詳細(前提条件等も)がほとんど書かれてないようにも見えますので。

[yuic.sk12345]

ありがとうございます！

`department_id` int(11) NOT NULL,としており、初期値は0となっております。
前提条件が見にくくてすみません。。。

先ほど、各カラムに以下のようなREPLACE関数を付けてみたのですが、やはり、すべて0（ゼロ）のレコードのdepartment_idは、表示されませんでした。。。

REPLACE(sum(case level_id when 1 then 1 else 0 end), 0, '-') as ０,

[m.ts10806]

失礼、集計後の表で存在しない場合にnullになるから、ということですね。

Answer 1

まず集計結果をサブクエリーにします。
部署ごとに表示したいなら部署に対してleft joinします
例示のように集計に対して部署をjoinするならright joinです

参考

集計のところを思いっきり省略するとこんな感じです

CREATE TABLE `t_general` (
  `id` int primary key auto_increment,
  `depa_id` int,
  `val` int
);
insert into t_general(depa_id,val) values
(1,100),
(1,200),
(1,300),
(2,100),
(2,150),
(3,120);

CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `depa` varchar(20)
); 
INSERT INTO `department` (`id`,`depa`) VALUES
(1, '----'),
(2, '総務'),
(3, '経理'),
(4, '庶務'),
(5, '人事部');

集計

select t1.id,t1.depa,coalesce(t2.val,0) as val
from department as t1
left join(
select depa_id,sum(val) as val from t_general
group by depa_id
) as t2 on t1.id=t2.depa_id

結果

id	depa	val
1	----	600
2	総務	250
3	経理	120
4	庶務	0
5	人事部	0

Comments

[yuic.sk12345]

ありがとうございます！
さっそく試してみます！！

[yuic.sk12345]

@yambejpさま
以下のSQLでできました！！

完璧です！！
本当に助かりました！！
ありがとうございます！！！

select
t1.id,
t1.depa,
coalesce(t2.department_id,0) as val,
coalesce(t2.合計,0) as 合計,
coalesce(t2.Ｉ計,0) as Ｉ計,
coalesce(t2.Ａ計,0) as Ａ計,
coalesce(t2.０,0) as ０,
coalesce(t2.１,0) as １,
coalesce(t2.２,0) as ２,
coalesce(t2.３ａ,0) as ３ａ,
coalesce(t2.３ｂ,0) as ３ｂ,
coalesce(t2.４,0) as ４,
coalesce(t2.５,0) as ５

from department as t1
left join(
select
department_id,COUNT(*) as 合計,
sum(case when level_id <= 4 then 1 else 0 end) as Ｉ計,
sum(case when level_id >= 5 then 1 else 0 end) as Ａ計,
sum(case level_id when 1 then 1 else 0 end) as ０,
sum(case level_id when 2 then 1 else 0 end) as １,
sum(case level_id when 3 then 1 else 0 end) as ２,
sum(case level_id when 4 then 1 else 0 end) as ３ａ,
sum(case level_id when 5 then 1 else 0 end) as ３ｂ,
sum(case level_id when 6 then 1 else 0 end) as ４,
sum(case level_id when 7 then 1 else 0 end) as ５
from t_general AS t2
group by department_id
) as t2 on t1.id=t2.department_id