同一列中的计数条件 [英] Count On Condition in same column

问题描述

经过很多搜索后，我必须在这里向专家咨询...

After searching a lot, I have to ask experts here...

表1（出席）

att_id    att_date    class_id   student_id   att_status
  1      07-11-2013       1          1           P
  2      07-11-2013       1          2           L
  3      07-11-2013       1          3           P
  4      07-11-2013       1          4           P
  5      08-11-2013       2          5           P
  6      08-11-2013       2          6           P
  7      08-11-2013       2          7           A
  8      08-11-2013       2          8           P
  9      09-11-2013       1          1           L
 10      09-11-2013       1          2           P
 11      09-11-2013       1          3           A
 12      09-11-2013       1          4           P
 13      09-11-2013       2          5           P

其中在att_status中P =存在，A =缺勤，L =离开。现在我想根据类ID 计算个别学生的现在，缺席和离开。

Where in att_status P=Present, A=Absence, L=Leave. Now I want to count individual student Present, Absent and Leave based on class id.

希望的结果

stu_id     class_id    Present    Absent   Leave
  1            1          1         0        1
  2            1          1         0        1
  3            1          1         1        0
  4            1          2         0        0
  5            2          1         0        0
  6            2          1         0        0
  7            2          0         1        0
  8            2          1         0        0

推荐答案

select student_id, 
       class_id, 
       sum(att_status='P') as present,
       sum(att_status='A') as absent,
       sum(att_status='L') as leave
from attendance
group by student_id, class_id

这篇关于同一列中的计数条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！