• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2238
  • Last Modified:

SQL COUNT DISTINCT problem

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
0
JimMaguire
Asked:
JimMaguire
  • 4
  • 3
  • 3
  • +2
1 Solution
 
sodakotahuskerCommented:
I think the problem might lie in the fact that Access does not support all SQL statements.  You can arrive at what you want like this:

select count(myfield) from table group by myfield

This will return a recordset with all the different distinct values.  Then the recordcount would tell you how many varieties you have.  This is much more inefficient than what you are trying to do since it retrieves all of the values.  

answer = recordset.recordcount

0
 
johnny6Commented:
JimMaguire:
           The SELECT statement has the following syntax:

SELECT [ALL|DISTINCT] <[table].columnA, [table].columnB>
FROM <tables>
WHERE <filters>
GROUP BY <columns used to combine records based on column value>
HAVING <filter on grouped records>
ORDER BY <columns to be sorted on>

The SELECT element has two parts. The first part states whether you want all rows returned (ALL) or just non-duplicate rows (DISTINCT).  While each row in a given table must be unique, often one only returns a subset of the columns. Therefore, there is a possibility of duplicates and the need for DISTINCT. The secons part lists the columns to be returned.

When listing the columns, we can also specify a title for the output.  For example, in Access we can specify <ColumnName> AS <Output Title> which will be used as the header in the resulting table.  (Note that the angled brackets represent a variable.)  In a query, the <ColumnName> will be replaced in the output by the specified <Output Title>.

Suppose we had a table called Students with a field LastName and we wanted to count the number of distinct last names then the SQL code would be as follows:

SELECT DISTINCT Count(Student.LastName) AS [Number of unique Students]
FROM Student;

I hope this clears up any questions you might have about SQL syntax.


John
0
 
PaulHewsCommented:
No that won't work, because the count function returns a single distinct value.

I had this problem before, the best way to get at it is to use a sub-query.

Select Count(myfield)
From table
Where myfield in (select distinct myfield from table where etc....)
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
PaulHewsCommented:
For reference, here's the question I posted back when I had that problem:
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172244
0
 
johnny6Commented:
PaulHews:
         You are correct.  I should have read his question more carefully before answering.
         I just checked out your problem and noticed that you had to do a lot of "tweaking" just to get the correct answer even after you had the correct idea.  Jim will also need to do some tinkering to get his query to work.

John
0
 
PaulHewsCommented:
>"tweaking"

LOL
0
 
sodakotahuskerCommented:
Johny6 is right.  PaulHews had the right idea but the wrong syntax cause it brings back the wrong answer.  This is what you need

SELECT Count(myfield) FROM  (select distinct myfield from mytable)
0
 
PaulHewsCommented:
No the example I gave was too simplistic.  Something like this will work however:

SELECT Count(tblA.ID) AS CountID
From tblA
Where tblA.ID in (Select distinct tblA.ID
FROM tblB INNER JOIN tblA ON tblB.ID = tblA.ID)

You can't use a select query in the FROM clause (at least not in Jet SQL.)

0
 
sodakotahuskerCommented:
Ahhhh!!   Seems we're both right PaulHews.  Jet 3.51 does not support this syntax.  But Jet 3.60 does - which is what I tried first.  
0
 
Anthony PerkinsCommented:
PaulHews,

>>You can't use a select query in the FROM clause (at least not in Jet SQL.)<<
This feature was introduced in Access 2000.  It was not available Access 97 (I assume you are using that version)

JimMaguire,
>>SELECT COUNT(DISTINCT myfield)<<
The following syntax is supported in T-SQL (SQL Server language):
COUNT([ALL | DISTINCT] expression)

As you have discovered this is not supported by the JET syntax.

Anthony
0
 
Anthony PerkinsCommented:
So to summarize, since you are using Access you cannot use the COUNT(DISTINCT myfield) syntax so depending on your version of JET you have three options:

1. sodakotahusker solution:
select count(myfield) from table group by myfield

This solution relies on the RecordCount property to return the actual count of distinct records.  Since depending on your cursor RecordCount is not always supported and when it is you will not get the best performance, I would stronly urge you to use one of the other two solutions proposed:
2. sodakotahusker solution:
SELECT Count(myfield) FROM  (select distinct myfield from mytable)
This solution will require Access 2000

3.PaulHews solution:
SELECT Count(tblA.ID) AS CountID
From   tblA
Where  tblA.ID in (Select distinct tblA.ID
                   FROM tblB INNER JOIN tblA ON tblB.ID = tblA.ID)
This solution is the best as you are not restricted to the version of your software.

Hope this clarifies,
Anthony
0
 
JimMaguireAuthor Commented:
Paul Hews' modified comment was the most bulletproof method that worked as a solution for me.  
Anthony summarised the comments quite nicely, since many of the comments would be useful depending on the version of Access, etc., so many thanks to all.
Judging from the "location" of the commenters, it seems possible that Acess subqueries in Jet 3.5 may not be effective east of the Rockies - all the fine SQL minds seem to be in PST!!  No wonder I was having such a problem (being from the East Coast) <grin>.

Jim Maguire
0
 
Anthony PerkinsCommented:
You may be confusing the location of EE (PST) with the location of the rest of the crowd (mine is CST, so not quite EST, but still "east of the Rockies").

Anthony
0
 
JimMaguireAuthor Commented:
Anthony,
Ahhhhhh! EE is PST.
My paranoia was unjustified.

BTW, your overview comment was crystal clear and had a great deal of perspective - the marks of a pro. Kudos.

Jim Maguire

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now