As part of a college's registration system, student attendance marks are stored in a database table - one row per mark which contains the mark and an activity reference (as well as other infomation such as date the mark was added, date of the lesson etc)

The activity ref (activity is effectivley a class) is designed to join with an activity table, which in turn is joined with a module table (module is a course) which in turn is linked to a student table. Finding a set of marks for the student is just a case of joining each table.

Currently to find a students attendance as a percentage I have to perform a select query which counts all marks present for a given student in a given module. Then perform a second count query which uses IN in the where clause to select only present marks. The two results are then divided to work out a percentage.

This isn't a very fast way of doing things however. For example say I want to find a percentage attendance for a course, or even an entire faculty the data the count is working on suddenly becomes very large (theres currently about 500,000 rows, but this will have doubled before too long)

The pages producing the percentages are having to perform these queries multiple times, for example to view the percentage attendance for each course in a given subject area or faculty, as a result they run very very slowly.

Is there a way I can combine these two queries into a single query?

For example if I was writing this in a program I would perform the two operations in one - incrementing a counter for each row that matches the WHERE clause, at the same time (i.e. with the same operation) incrementing a second counter only if the mark is a present mark. (I hope that makes sense)

e.g.
PHP Code:
foreach($rows as $row) {
  
$all_marks++;

  if (
$row['mark'] == 'Present') {
    
$present_marks++;
  }

}

$percent_present = ($present_marks $all_marks) * 100
Is there a way to have the database return the two results in a single operation, preferably using the same set of data - so that I only have to run one query?

I'm using stored procedures and have tried optimising the query, but the fact that I'm having to run it twice means that I'm suffering a severe performance hit.

Any suggestions / advice are greatly appreciated.

Rick