Solved

SQL COUNT DISTINCT problem

Posted on 2002-07-06
14
2,144 Views
Last Modified: 2007-11-27
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
Comment
Question by:JimMaguire
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 1

Expert Comment

by:sodakotahusker
ID: 7134326
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
 
LVL 2

Expert Comment

by:johnny6
ID: 7134346
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 7134356
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 38

Expert Comment

by:PaulHews
ID: 7134357
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
 
LVL 2

Expert Comment

by:johnny6
ID: 7134373
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 7134383
>"tweaking"

LOL
0
 
LVL 1

Expert Comment

by:sodakotahusker
ID: 7134621
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
 
LVL 38

Accepted Solution

by:
PaulHews earned 100 total points
ID: 7134680
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
 
LVL 1

Expert Comment

by:sodakotahusker
ID: 7135024
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7135102
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7135129
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
 

Author Comment

by:JimMaguire
ID: 7136290
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7136372
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
 

Author Comment

by:JimMaguire
ID: 7137627
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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now