JimMaguire
asked on
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
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
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
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
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....)
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....)
For reference, here's the question I posted back when I had that problem:
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172244
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20172244
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
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
>"tweaking"
LOL
LOL
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)
SELECT Count(myfield) FROM (select distinct myfield from mytable)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
>>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
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
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
ASKER
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
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
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
Anthony
ASKER
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
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
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