How to count unique values only?

[ppataki]

Dear All,

Could someone please tell me how I can count only unique values in a field using only one query?

At the moment I do it with two queries, the first makes a Group by, to get the unique values, then the second makes a Count on the results of the first query. But I think there must be a simpler way with one query only

Many thanks in advance

 

[ByteMyzer]

Try the following SQL (substitute highlighted text with actual table/field names):

SELECT COUNT(*)
FROM (SELECT DISTINCT T1.[[b][i]MyField[/i][/b]]
      FROM [[b][i]MyTable[/i][/b]] AS T1
     ) AS T1;

 

[ppataki]

Thank you very much it works perfectly!!!

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you.

 

[sladetroityer]

Will this formula work on a report?

 

[bmcneill]

This also worked for a student database that I am working on. My additional challenge is that I need to query specific date ranges for unique student id values for reporting purposes. Any advice?

 

[godknow]

Thanks, this helped me ...