The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Speeding up two Select (Count) Queries

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.

I'll give that a try - I could replace the in( 101,356...) part with code to select each student based on a student to class lookup table, I could probably replace the overall percentage query with something in the ASP code that averages all the student percentages. I'll no doubt be posting here again when I get stuck anyway!