SQL countで取得する値の重複を排除して表示させたい

出席率を集計するSQL

前提・実現したいこと
出席状況(lecture_attend_logs.attend_status)を重複を排除して表示させたい

起きている問題
テーブルを追加したことにより、下記の値が重複して表示される

select
   count(lecture_attend_logs.attend_status) = "attended" or null) as "出席(授業数)",
   count(lecture_attend_logs.attend_status = "absented" or null) as "欠席(授業数)",
   count(lecture_attend_logs.attend_status = "late" or null) as "遅刻数",
   count(lecture_attend_logs.attend_status = "late" or null) DIV 3 as "遅刻分欠席(3回で欠席1回)",
   count(lecture_attend_logs.attend_status = "public_absented" or null) as "公欠(授業数)",
   count(lecture_attend_logs.attend_status = "closed" or null) as "休校",
   ROUND((count(lecture_attend_logs.attend_status = "attended" or null ) + (count(lecture_attend_logs.attend_status = "late" or null) - 
   count(lecture_attend_logs.attend_status = "late" or null) DIV 3) + 
   count(lecture_attend_logs.attend_status = "public_absented" or null) + 
   count(lecture_attend_logs.attend_status = "closed"or null ))  / 
   count(lecture_attend_logs.attend_status),5) * 100 as "出席率合計(%)"
from
　lecture_attend_logs
    join lectures on lecture_attend_logs.lecture_id = lectures.id
    join students on lecture_attend_logs.student_id = students.id
    join klass_subjects on lectures.klass_subject_id = klass_subjects.id
    join klasses on klasses.id = klass_subjects.klass_id
    join student_japanese_schools on students.id = student_japanese_schools.student_id
    join japanese_schools on japanese_schools.id = student_japanese_schools.japanese_school_id
    join klass_students on klass_students.klass_id = klasses.id

試したこと
上記のcountにdistinctを追加して重複を排除しようと試みたのですが、attend_statusの重複が排除されるため、1が出力される

よろしくお願いします。

行動規範の内容に同意します

回答1件

一度，結合して重複を取り除いたテーブルに対してcountするのはどうでしょうか?

試してないのでうまくいくかどうか分かりませんが，次のような感じです。(副問い合わせというそうです)

attend_statusカラムがlecture_attend_logsテーブル以外にもある場合はうまくいかないと思いますが，
そうでなければいけそうな気もします。

sql
1select
2   count(attend_status = "attended" or null) as "出席(授業数)",
3   count(attend_status = "absented" or null) as "欠席(授業数)",
4   count(attend_status = "late" or null) as "遅刻数",
5   count(attend_status = "late" or null) DIV 3 as "遅刻分欠席(3回で欠席1回)",
6   count(attend_status = "public_absented" or null) as "公欠(授業数)",
7   count(attend_status = "closed" or null) as "休校",
8   ROUND((count(attend_status = "attended" or null ) + (count(attend_status = "late" or null) - 
9   count(attend_status = "late" or null) DIV 3) + 
10   count(attend_status = "public_absented" or null) + 
11   count(attend_status = "closed"or null ))  / 
12   count(attend_status),5) * 100 as "出席率合計(%)"
13from
14(select distinct * from lecture_attend_logs
15    join lectures on lecture_attend_logs.lecture_id = lectures.id
16    join students on lecture_attend_logs.student_id = students.id
17    join klass_subjects on lectures.klass_subject_id = klass_subjects.id
18    join klasses on klasses.id = klass_subjects.klass_id
19    join student_japanese_schools on students.id = student_japanese_schools.student_id
20    join japanese_schools on japanese_schools.id = student_japanese_schools.japanese_school_id
21    join klass_students on klass_students.klass_id = klasses.id
22)