close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

COUNT(*) Function

Thread began 9/03/2015 5:19 am by salaroche | Last modified 10/05/2015 2:31 pm by Ray Borduin | 814 views | 21 replies |

salaroche

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

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post

Nathon Jones Web Design

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

Sign in to reply to this post

salaroche

Nathon:

No problem. 2 recordsets sounds OK too.

Many thanks for your attention.

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

salaroche

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...