title: Mysql Pivot table for attendance per weekdays of week
body: <p>for a date interval, I want to display the attendance per weekdays of students records, recorded in three tables [Attendance, Students, Person].
  The schema (relevant fields) of tables:</p>
  
  <blockquote>
  <pre><code>Attendance Table
  --------------------- 
  Attendance_Identifier 
  Student_Identifier 
  Classroom_Identifier 
  Attendance_Datetime 
  Attendance_Value 
  ...
  </code></pre>
  </blockquote>
  
  <pre><code>Student
  ------------------
  Student_Identifier
  Person_Identifier
  Classroom_Identifier
  ...
  
  Person
  -----------------
  Person_Identifier
  Frist_Name
  Last_Name
  Gender
  ...
  </code></pre>
  
  <p><strong>Expected Report Output:</strong></p>
  
  <blockquote>
  <pre><code>Gender    Student     Mon     Tue     Wed     Thu     Fri     WeeklyTotal
  ------------------------------------------------------------------------
  Male     Ab Stain     2/10    3/12    1/9     1/10    0/10    7/51
  Male     Pre Senter   10/10   12/12   9/9     9/10    10/10   50/51
  ...
  Female    Al Ways     10/10   12/12   9/9     10/10   10/10   51/51
  Female    Not Often   5/10    5/12    4/9     4/10    5/10    23/51
  ...
  </code></pre>
  </blockquote>
  
  <p>I also have a function to get the count of particular day of week from a date interval say for total Mondays in date interval @s, @e, just do:  <code>select get_weekday(@s,@e,0).</code></p>
  
  <p>So my query for my stored procedure is :</p>
  
  <pre><code>set @s = '2016-01-01';
  set @e = '2016-12-07';
  SELECT 
  concat(p.Frist_Name, ' ',p.Last_Name) as Student, p.Gender
      ,GROUP_CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Mon' 
          THEN CONCAT(COUNT(Attendance_Value),'/',get_weekday(@s,@e,0)) ELSE NULL END) AS Mon
      ,GROUP_CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Tue' 
          THEN CONCAT(COUNT(Attendance_Value),'/',get_weekday(@s,@e,1)) ELSE NULL END) AS Tue
      ,GROUP_CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Wed' 
          THEN CONCAT(COUNT(Attendance_Value),'/',get_weekday(@s,@e,2)) ELSE NULL END) AS Wed
      ,GROUP_CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Thu' 
          THEN CONCAT(COUNT(Attendance_Value),'/',get_weekday(@s,@e,3)) ELSE NULL END) AS Thu
      ,GROUP_CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Fri' 
          THEN CONCAT(COUNT(Attendance_Value),'/',get_weekday(@s,@e,4)) ELSE NULL END) AS Fri
      , SUM(COUNT(Attendance_Value)) as WeeklyTotal
  FROM Attendance a JOIN Student s ON s.Student_Identifier=a.Student_Identifier 
  JOIN Person p ON p.Person_Identifier=s.Person_Identifier 
  WHERE date(Attendance_Datetime) BETWEEN @s AND @e AND a.Classroom_Identifier = '363'
  AND (Attendance_Value = 'Present' OR Attendance_Value = 'Late') AND (p.Gender = 'Male' OR p.Gender = 'Female') 
  AND DATE_FORMAT(Attendance_Datetime, '%a') !='Sat' AND DATE_FORMAT(Attendance_Datetime, '%a')!='Sun' 
  GROUP BY p.Gender, Student, WeeklyTotal ORDER BY p.Gender, Student;
  </code></pre>
  
  <p>And I get the following group function error each time even with many tweaks to the code.</p>
  
  <pre><code>Error Code: 1111. Invalid use of group function
  </code></pre>
  
  <h2>Edit</h2>
  
  <p>Based on @Solarflare 's first suggestion (I don't know how to try the second one), I got the query refined to:</p>
  
  <pre><code>SELECT 
  concat(p.Frist_Name, ' ',p.Last_Name) as Student, p.Gender
      ,CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Mon' 
          THEN COUNT(Attendance_Value) ELSE 0 END,'/',get_weekday(@s,@e,0) ) AS Mon
      ,CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Tue' 
          THEN COUNT(Attendance_Value) ELSE 0 END,'/',get_weekday(@s,@e,1) ) AS Tue
      ,CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Wed' 
          THEN COUNT(Attendance_Value) ELSE 0 END,'/',get_weekday(@s,@e,2) ) AS Wed
      ,CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Thu' 
          THEN COUNT(Attendance_Value) ELSE 0 END,'/',get_weekday(@s,@e,3) ) AS Thu
      ,CONCAT(CASE WHEN DATE_FORMAT(Attendance_Datetime, '%a') = 'Fri' 
          THEN COUNT(Attendance_Value) ELSE 0 END,'/',get_weekday(@s,@e,4) ) AS Fri
      , COUNT(Attendance_Value) as WeeklyTotal
  FROM Attendance a JOIN Student s ON s.Student_Identifier=a.Student_Identifier 
  JOIN Person p ON p.Person_Identifier=s.Person_Identifier 
  WHERE date(Attendance_Datetime) BETWEEN @s AND @e AND a.Classroom_Identifier = '363'
  AND (Attendance_Value = 'Present' OR Attendance_Value = 'Late') AND (p.Gender = 'Male' OR p.Gender = 'Female') 
  AND DATE_FORMAT(Attendance_Datetime, '%a') !='Sat' AND DATE_FORMAT(Attendance_Datetime, '%a')!='Sun' 
  GROUP BY Student,Gender,DATE_FORMAT(Attendance_Datetime, '%a') ORDER BY p.Gender, Student,Mon DESC;
  </code></pre>
  
  <p>and the result is as below:</p>
  
  <p><a href="https://i.stack.imgur.com/Ar6JJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Ar6JJ.png" alt="enter image description here"></a></p>