"Type" is your table's category or type field and NumberofType is simply the name you give the count so that you can then display it on your page. You can call this anything you like. You need to use GROUP BY to group the types.

I'll assume this is a recordset you're creating, in which case you'd then display the count on the page as:<?php echo($RECORDSETNAME->getColumnVal("NumberofType")); ?>

Thanks for your reply. I’ll try your suggestion and get back to you later.

UPDATE: BTW: where would the WHERE clause go? The COUNT function has to select a specific kind of records. Selecting by Type is OK, but the idea is to count a subset of instances within that Type, hence the WHERE clause.

Sorry, Maybe I didn’t make myself clear. The recordset in question is retrieving all records from table oxadmain. The COUNT() function is needed to count a subset of records in order to perform calculations using other data from oxadmain.

The purpose of the recordset is NOT solely to count records, but to display info and perform operations using the results.

I appreciate the solutions presented by Nathon, however, I would still have the problem of how to display the results from two different recordsets in one single results page.

The operation I’m trying to implement involves the following: 1) SELECT records that have a set of characteristics “A” and perform an operation with them and 2) SELECT records that have a set of characteristics “B” and perform a DIFFERENT operation with them. NOTE: the latter operation ("B") involves the results of a COUNT() function. Finally, display BOTH sets of records in ONE single display table using a REPEATED REGION.

Maybe the way out would be to use an IF statement, which in sql/php language seems to be the equivalent of CASE. But even in this case I would still need to use the COUNT() function with a WHERE clause.

It would be easier to give suggestions if you were more specific about what you were trying to do.

You could potentially do a nested select statement like:SELECT *, (SELECT Count(*) FROM oxadmain as OA2 WHERE OA2.column = 'value') AS countedSubsetFROM oxadmainORDER BY oxadmain.name ASC

However that can be pretty inefficient depending on how many rows are returned. Another solution is a second Recordset as Nathon suggests. You can nest the second Recordset inside the repeated region so that it can be filtered with parameters from the first and do a count for each row.

Thank you for your input. Sorry for the perceived lack of clarity. Here’s exactly (I hope) what I’m trying to do:

A table in the db (oxadmain) contains hundreds of student attendance records, of which some include the characteristic “Absent”. The task is to show in a WADAResults page all the records that contain “Absent” and all those that don’t, except that the ones that contain the “Absent” characteristic also need to show the percentage of times each student was absent from class, hence the need for a COUNT() function, so that I can calculate the percentage.

% of Absence = Number of Absent records, divided by total number of class days, times 100.

Both the records that have an “Absent” characteristic and those that don’t, need to be shown on the same results page/table, which contains a REPEAT REGION.

In fact, it seems that two COUNT() functions might be needed, one to count the total number of records-per-student entered and another one to count the number of records where the student was “Absent”.