Writing A Select Statement To Return a Count Of Unique Dates In aTable

[R Tanner]

Hi,

I'm trying to write a query that will return a count of unique Dates
in a table. The following is what I have been trying to use thus
far. I just can't seem to get it quite right. I have found my sub
sql statement will return all records if I select the ID. For
example, if I change the subquery to 'SELECT DISTINCT DAT, RFCID FROM
Instances', it works fine. The only problem being the RFCID column
does not contain unique values.

SELECT Count(*) AS TotalUniqueCalls
FROM Instances
WHERE ID IN (SELECT DISTINCT DAT, ID FROM Instances);

 

[Michel Walsh]

DISTINCT is applied to ALL the fields in the SELECT clause, as if they were
ONE entity. So, SELECT DISTINCT dat, rfcid FROM ... will return DISTINCT
COUPLES made of (dat, rfcid).

Next, it seems you need that subquery in the FROM clause:


SELECT COUNT(*) AS countOfDistinctID
FROM (SELECT DISTINCT id FROM instances) AS a


as example (where I removed the unrelated field "dat" ).


Vanderghast, Access MVP

 

[R Tanner]

DISTINCT is applied to ALL the fields in the SELECT clause, as if they were
ONE entity. So, SELECT DISTINCT dat, rfcid FROM ... will return DISTINCT
COUPLES made of (dat, rfcid).

Next, it seems you need that subquery in the FROM clause:

SELECT COUNT(*) AS countOfDistinctID
FROM (SELECT DISTINCT id FROM instances) AS a

as example (where I removed the unrelated field "dat" ).

Vanderghast, Access MVP

oh perfect...I guess I didn't realize you could put a sub query in a
from clause. I am just starting to get into sql statements that are
not just the straight cut SELECT FROM WHERE kinda stuff...Thanks
again...