Solved

SQL COUNT DISTINCT problem

Posted on 2002-07-06
14
2,136 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

747 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

11 Experts available now in Live!

Get 1:1 Help Now