MySQL 出席情報を表示させたい

Question

前提・実現したいこと

SQLで出席率、授業数、出席数、欠席数、遅刻数を表示させたい

発生している問題

下記を実行すると結果が2倍〜9倍の数が表示されます。

select
 s.name,
 count(lal.id) as "授業数",
 count(lal.attend_status = "attended" or null) as "出席数(授業数)",
 count(lal.attend_status = "absented" or null) as "欠席数(授業数)",
 count(lal.attend_status = "late" or null)/3 as "遅刻数",
 ROUND((count(lal.attend_status = "attended" or null ) + count(lal.attend_status = "public_absented"or null ) + count(lal.attend_status = "late"or null )/3  + count(lal.attend_status = "closed"or null ))  / count(lal.attend_status),2) * 100 as "出席率(%)"
from
  students s 
  left outer join lecture_attend_logs lal on lal.student_id = s.id
  left outer join lecture_students ls on s.id = ls.student_id
  
  left outer join lectures l on l.id = ls.lecture_id
  left outer join course_category_students ccs on s.id = ccs.student_id
  left outer join course_categories cc on cc.id = ccs.course_category_id
  left outer join klass_students ks on s.id = ks.student_id
Where 
s.id = 6550
group by lal.student_id

![]

試したこと

重複データ分もカウントしているのではと考え、distinctを追記したのですが該当箇所の値が1になります。

select
s.name,
count(distinct lal.id ) as "授業数",
	count(distinct lal.attend_status = "attended" or null) as "出席数(授業数)",
	count(distinct lal.attend_status = "absented" or null) as "欠席数(授業数)",
	count(distinct lal.attend_status = "late" or null)/3 as "遅刻数",
	ROUND((count( distinct lal.attend_status = "attended" or null ) + count(distinct lal.attend_status = "public_absented"or null ) + count(distinct lal.attend_status = "late"or null )/3  + count(distinct lal.attend_status = "closed"or null ))  / count(distinct lal.attend_status),2) * 100 as "出席率(%)"
from
  students s 
  left outer join lecture_attend_logs lal on lal.student_id = s.id
  left outer join lecture_students ls on s.id = ls.student_id
  left outer join lectures l on l.id = ls.lecture_id
  left outer join course_category_students ccs on s.id = ccs.student_id
  left outer join course_categories cc on cc.id = ccs.course_category_id
  left outer join klass_students ks on s.id = ks.student_id
Where 
s.id = 6550
group by lal.student_id

Comments

[m.ts10806]

テーブル定義をご提示ください

[sazi]

流石に、キー構成とサンプルデータが無いと、SQLだけでは、どこで増殖しているのか分かりません。

> 下記を実行すると結果が2倍〜9倍の数が表示されます。
上記のサンプルになっていませんよね？

Answer 1

明らかにおかしいのは、
lal.attend_status = "attended" or nullのような判定です。
正しくは、以下の様にすべきでは？
lal.attend_status = "attended" or lal.attend_status is null
または
~~coalesce(lal.attend_status, "attended") = "attended"
~~
そもそもNUllの判定必要ですか？

よく見ると、真偽式の結果をCOUNT()しているのもおかしいですね。
真偽式の(True/False=1/0)を利用するなら、Sum()を使用します。

True or NUll → True
false or Null → Null
Null or Null → Null
ですね。
指摘は誤りでした。