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