count (group by) problem

[necta]

I have a table below, I would like to count the eID by group to know how many eID group will 115 have. So, it is suppose to have 3 groups:X (1), Y(2) and z(1). In the end, recordcount will show there are 3 groups for eID refer to ID 115.
TblX
ID eID COUNT(eID)
115 X -------> 1
115 Y -------> 2
115 Y
116 Y
115 Z ------> 1

Code snippet:
Set wRS = Server.CreateObject("ADODB.Recordset")
sql1="SELECT eID,COUNT(eID) AS count FROM TblX GROUP BY eID WHERE ID='"&ID&"'"
wRS.Open sql1,wCON,1,1,1

But it shows: Syntax error (missing operator) in query expression 'eID WHERE eID='115''.

I wonder what could be wrong. Any help is very much appreciated.

 

[onpnt]

try this construct

SELECT fieldlist
FROM table
WHERE selectcriteria
GROUP BY groupfieldlist
HAVING groupcriteria

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[onpnt]

more info also at asp-help sql_ref

now that I found it

looks your exact query is listed 80% down the page actually

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[necta]

Hi,onpnt,
Thanks for the link. I swapped the swap the GROUP BY and WHERE clause and it works. But I have another problem. I open the recordset with:
wRS.Open sql1,wCON,1,1,1

but the following error appeared:
"ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. "

When I used set wRS=wCON.Execute(sql1). No error appeared.
But the recordCount gave -1. What I have done wrong? Thanks.

 

[onpnt]

should help you out on that (CursorType/LockType need adjusting)

thread333-734059

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page