Simple count?

[rjoseph]

Hi Guys

I need to do display the total number of matching records from the script below:

sSQL = "SELECT make, model, description, colour, price FROM MyTable"
sSQL = sSQL & " WHERE make = 'Aston Martin'"
sSQL = sSQL & " AND model = 'DB7'"

So, if I have 14 Aston Martins in my database then it would simpy display "14". I have tried ......

sSQL = "SELECT make, COUNT(make) AS Total, model, description, colour, price FROM MyTable"
sSQL = sSQL & " WHERE make = 'Aston Martin'"
sSQL = sSQL & " AND model = 'DB7'"

but I get a " Column 'make' is invalid in the select
list because it is not contained in either an aggregate function or the GROUP
BY clause" error.

Any help would be fully appreciated

Best regards

Rod from the UK

[jhardman]

this is really more of a SQL question, but it is pretty easy to understand. Basically, when you use "aggregate functions" like count, sum, average, etc, you can't mix those with non-aggregated fields from the db. There are a couple ways around this: 1- you can add the non-aggregated field to a "group by" clause, the db will think you are trying to group everything by "make" and give you the aggregated function within that group:

SELECT make, count(make) from MyTable where ... Group By make

result:
make            no name
Aston Martin  14

The downside of this approach is that you will need to add each of the non-aggregated fields you want to show up into the group by clause.

2- pull up the result in a recordset object and use the property objRS.recordcou nt to get the count

set objRS = server.createobject(adodb.recordset)

objRS.open sSQL, objCon

response.write "Your search found " & objRS.recordcount & " records."

Does this help?

Jared

[rjoseph]

Brilliant

Thanks Jared!