How do you do a COUNT DISTINCT on multiple fields? 1

[danno64]

I want to be able to retrieve the number of distinct rows in a table using multiple fields as the criteria for the Distinct keyword.

SELECT DISTINCT field1, field2, field3 FROM Table1
...gives a row listing of each distinct combination of
field1, field2, and field3.

But what I want is just the number of distinct combinations....

SELECT COUNT(DISTINCT field1, field2, field3) FROM Table1

...does not work.

Any ideas?

The solution is probably easy, but I'm drawing a blank....

 

[r937]

select count(*)
  from (
       select distinct 
              field1
            , field2
            , field3
         from table1 
       ) foo

rudy

http://r937.com/

SQL Consulting

 

[danno64]

Thank-you r937.

I take it that "foo" is just some arbitray name that TSQL
needs to execute the command?

 

[r937]

yep

rudy

http://r937.com/

SQL Consulting

 

[pullingteeth]

Hello, is this an Access 2k+ feature? In Access 97, I can't get any permutation of count distinct to work.

I've tried:

select count(distinct le.timestamp) from le

and

select count(*) from (select timestamp from le) foo

What I'd really like to do is something like:

select count(distinct int(timestamp)) 
from le 
where user='john' and timestamp between #date1# and #date2#

Can someone translate the latter into working code?

Thanks

 

[ESquared]

Access won't allow you to use derived tables, which are complete select statements inside parentheses which act like a table.

Instead, you have to write a separate query, save it, and refer to it as a table.

I think you've been given enough information by everyone now that you can make it work!

 

[pullingteeth]

<I>I think you've been given enough information by everyone now that you can make it work!</I>

Huh? The previous solutions in this thread *don't* work in Access 97 (my second code snippet replicates the solution given in this thread). I know I can have a seperate query, and refer to that, but that isn't a tidy (read: maintainable) solution in a mid-sized application. My "solution" to this problem is currently to count the records in the recordset using VBA code, but this obviously isn't great for performance in large scale queries.

 

[ESquared]

The previous solutions in this thread *don't* work in Access 97

That could be because this is the Microsoft SQL Server: Programming Forum.

Perhaps you could try the Microsoft: Access Queries and JET SQL forum or the Microsoft: Access Modules (VBA Coding) forum.

I've never done it, but maybe you can do a pass-through query in Access where the query actually gets executed on SQL server and Access just retrieves the rowset. But I don't know how to do that... ask the Access forum folks.

 

[pullingteeth]

<I>That could be because this is the Microsoft SQL Server: Programming Forum.</I>

HA! My bad, I'm sorry. (FWIW I picked up this thread through "Advanced Search").

I have used both SQL Server and Oracle in the past...and now I'm stuck with Access. It's *so* frustrating.

 

[ESquared]

Use [ignore][/ignore]square brackets[ignore][/ignore] around markup tags...

 

[ESquared]

Can you blame me for being bad-tempered sometimes?