Counting records with Count(*) AS

Counting records with Count(*) AS

Post by j.. » Wed, 24 Sep 1997 04:00:00



I am having problems creating a recordset and counting the number of
records therein at the time.

Lets say Database name = db
Table we're interested in = Table
Fileds we're interested in are x, y and z

So what's wrong with

Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
TotalNumber, x, y, z FROM Table WHERE x = " & whatever

I keep getting the message "x  not part of aggregate function or
grouping"

If I remove Count(Table.x) AS TotalNumber, rsRecordsetName opens fine.

If I create a second recordset with just SELECT Count ... I get the
count.

How, please, do I put the two together.

Jonathan

 
 
 

Counting records with Count(*) AS

Post by Thomge » Wed, 24 Sep 1997 04:00:00


Quote:>Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
>TotalNumber, x, y, z FROM Table WHERE x = " & whatever

>I keep getting the message "x  not part of aggregate function or
>grouping"

You can add Table. to each of the column references, i.e.
    "SELECT Count(Table.x) AS TotalNumber, Table.x, Table.y, Table.z from
...

 
 
 

Counting records with Count(*) AS

Post by Ganapathy Subramania » Wed, 24 Sep 1997 04:00:00


How does it make sense to have them both selected?

Example:
X       Y       Z
1       1       1
2       3       4
3       4       5

Select Count(x) From Table gives you
3

Select Count(x) From Table gives you (Hypothetical)
3       1       1       1
3       2       3       4
3       3       4       5

Is this what you want ????

 
 
 

Counting records with Count(*) AS

Post by Lance Kin » Wed, 24 Sep 1997 04:00:00


You need to have a Group By and Having clause:

"SELECT Count(x) AS TotalNumber, x
FROM Table1
GROUP BY x
HAVING x = " & Whatever

OR

"SELECT Count(x) AS TotalNumber, x, y, z
FROM Table1
GROUP BY x, y, z
HAVING x = " & Whatever

Notice the different results you will get with each of the above depending
on whether or not y and z have different values for the same values of x.
(Does that make sense?)
--
Lance King
Work: 770-801-4502

Take out "nos.pam." to send message.



Quote:> I am having problems creating a recordset and counting the number of
> records therein at the time.

> Lets say Database name = db
> Table we're interested in = Table
> Fileds we're interested in are x, y and z

> So what's wrong with

> Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
> TotalNumber, x, y, z FROM Table WHERE x = " & whatever

> I keep getting the message "x  not part of aggregate function or
> grouping"

> If I remove Count(Table.x) AS TotalNumber, rsRecordsetName opens fine.

> If I create a second recordset with just SELECT Count ... I get the
> count.

> How, please, do I put the two together.

> Jonathan

 
 
 

Counting records with Count(*) AS

Post by Ray Henr » Thu, 25 Sep 1997 04:00:00


 Try this:

SELECT Count(Table.x) AS TotalNumber FROM Table WHERE x = " & whatever


Quote:>I am having problems creating a recordset and counting the number of
>records therein at the time.

>Lets say Database name = db
>Table we're interested in = Table
>Fileds we're interested in are x, y and z

>So what's wrong with

>Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
>TotalNumber, x, y, z FROM Table WHERE x = " & whatever

>I keep getting the message "x  not part of aggregate function or
>grouping"

>If I remove Count(Table.x) AS TotalNumber, rsRecordsetName opens fine.

>If I create a second recordset with just SELECT Count ... I get the
>count.

>How, please, do I put the two together.

>Jonathan

 
 
 

Counting records with Count(*) AS

Post by Cammy Hulse » Sat, 27 Sep 1997 04:00:00



> I am having problems creating a recordset and counting the number of
> records therein at the time.

> Lets say Database name = db
> Table we're interested in = Table
> Fileds we're interested in are x, y and z

> So what's wrong with

> Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
> TotalNumber, x, y, z FROM Table WHERE x = " & whatever

> I keep getting the message "x  not part of aggregate function or
> grouping"

> If I remove Count(Table.x) AS TotalNumber, rsRecordsetName opens fine.

> If I create a second recordset with just SELECT Count ... I get the
> count.

> How, please, do I put the two together.

> Jonathan

   Jonathan, I Think you may need to put the specific table reference
every time you use the 'x'. Such as:
Set rsRecordsetName = db.openrecordset("SELECT Count(Table.x) AS
TotalNumber, [Table].x, y, z FROM Table WHERE [Table].x = " & whatever

I hope this helps.