COUNT(*) Function

Hi:

I have the following simple query:

SELECT *
FROM oxadmain
ORDER BY oxadmain.name ASC

Now I would like to add a COUNT(*) function with a WHERE clause to find out how many records of A SPECIFIC KIND are returned. I’ve tried a couple of ways but they all return a syntax error.

What would be the right syntax for this?

Thank you for your attention

SELECT ID, Title, COUNT(Type) AS NumberofType
FROM oxadmain
GROUP BY Type

"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")); ?>

HTH
NJ

Nathon:

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.

Any suggestion?

Thank you for your attention.

It depends what you're querying. If it's records with the Title "John", for example, then it would be...

SELECT ID, Title, COUNT(Type) AS NumberofType
FROM oxadmain
WHERE Title = "John"
GROUP BY Type

Nathon:

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.

How would the syntax be in this case?

Thank you for your attention.

Not clear myself now!

If it's a subset then maybe you need 2 recordsets? One to do the count and one to display all the info? Both would have the same WHERE clause.

This'll teach me for trying to be smart!
NJ

Nathon:

No problem. 2 recordsets sounds OK too.

Many thanks for your attention.

Hi:

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.

Any suggestions on this?

Thank you for your attention.

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 countedSubset
FROM oxadmain
ORDER 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.

Ray:

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”.

I hope this time the explanation was much clearer.

Any suggestions?

Thank you for your attention.