Solved
SQL COUNT DISTINCT problem
Posted on 2002-07-06
Having a problem in VB6 with an SQL statement involving COUNT(DISTINCT myfield).
The recordset I’m dealing with is from a MS Access.mdb, and it can be represented as tblA.ID (the autonumber field) related to tblB.B_ID (non-unique);
let’s say there are 3 unique ID’s in tblA and 2 records in tblB for each unique ID in tblA. The recordset I’m using thus has 6 records (the actual RS has 4 tables interrelated and all the fields have unique names, and the ID field name isn’t used twice; but you get the idea.) Also, I’m working in the DataEnvironment of VB6, and a hierarchical command is not possible here as a workaround. Anyway, I already have a workaround; I’d like to solve the SQL problem!!
I'd like to get the following with SQL:
-a distinct count of the unique values in tblA (which I can get anyway from a .ListCount property from a combo box of unique names attached to tblA.ID
-as well as a count of all the records (which I can get from the RS.RecordCount property)
I have a pretty great book: SQL, the Complete Reference, by Groff and Weinberg, where there is a rather simple looking example stating:
SELECT COUNT(DISTINCT myfield)
However when I type the following in the SQL builder:
SELECT COUNT(DISTINCT myfield)
I get: missing operator !@#$%^&*
The following all produced successful SQL results in the VB SQL Builder, but obviously didn’t combine the two keywords COUNT and DISTINCT which I need.
SELECT COUNT(myfield) works to produce a non-distinct count of all records
SELECT DISTINCT (myfield) works to produce a distinct list of myfield values
SELECT DISTINCT myfield works to produce a distinct list of myfield values also
The latter worked even without the parentheses!
So then I sneaked up on the SQL builder and added the second keyword, but it knew what I wanted (or that I can miss the obvious) and I got:
SELECT COUNT DISTINCT (myfield) missing operator
SELECT COUNT(DISTINCT `myfield`) missing operator
SELECT COUNT(DISTINCT `tblA`.myfield) missing operator
SELECT COUNT(DISTINCT (myfield)) undefined function 'DISTINCT'
OKAY – I’M NOT PROUD!! What simple thing am I missing??
Jim Maguire