Calculate total attendance for each workshop I want to get overall attendance in each workshop. NumberOFWeeks column holds the number of weeks the workshop runs. for example Workshop 101 runs for 6 weeks meaning 6 days.

So far I have done this which gives me the total number of entries but how can I only include results for selective days. For example only count attendance on 01/04/2015, 02/04/2015, 05/04/2015 only


  SELECT
  COUNT(attendance.AttendanceID) as 'Total Attendance'
  FROM
  student
  INNER JOIN attendance ON attendance.StudentID = student.StudentID
  WHERE
  student.WorkshopID = '101'
Something like this would give you the total across all three of those days:

SELECT COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'


If you wanted the total separately for each of those dates, use this:

SELECT attendance.Date, COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'
GROUP BY attendance.Date


Try this for overall attendance. Keep in mind I didn't have time to setup the schema, so this is untested:

SELECT wsdata.WorkshopID
  , wsdata.TotalAttendanceDaysPossible
  , adata.TotalAttendanceDaysActual
  , ((adata.TotalAttendanceDaysActual / wsdata.TotalAttendanceDaysPossible) * 100) AS OverallAttendance
FROM (
  SELECT ws1.WorkshopID
    , ws1.NumberOfWeeks AS TotalDays
    , COUNT(s1.StudentID) AS TotalStudents
    , ws1.NumberOfWeeks * COUNT(s1.StudentID) AS TotalAttendanceDaysPossible
  FROM workshop ws1
  INNER JOIN student s1 on ws1.WorkshopID = s1.WorkshopID
  GROUP BY ws1.WorkshopID
    , ws1.NumberOfWeeks
) wsdata
INNER JOIN (
  SELECT s2.WorkshopID
    , COUNT(a.AttendanceID) AS TotalAttendanceDaysActual
  FROM attendance a
  INNER JOIN student s2 ON a.StudentID = s2.StudentID
  GROUP BY s2.WorkshopID
) adata ON wsdata.WorkshopID = adata.WorkshopID
WHERE wsdata.WorkshopID = '101'