Count On Condition in same column

问题:

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

Table 1 (attendance)

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

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.

Desired Result

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

回答1:

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