Count distinct in 1 query

[Jason Morin]

Assuming I have Table1 with 4 fields (a,b,c,d), I'd like
to run the following SQL statement, but I want a *unique*
count of field "b" for every value found in "a", not just
a regular count:

SELECT Table1.a, Count(Table1.b) AS CountOfb, Count
(Table1.c) AS CountOfc, Sum(Table1.d) AS SumOfd
FROM Table1
GROUP BY Table1.a;

Thanks.
Jason

 

[Michel Walsh]

Hi,


TRANSFORM COUNT(*) As c
SELECT a, COUNT(*) AS StandardCount, COUNT(c) As DistinctCount
FROM myTable
GROUP BY a
PIVOT someField IN(null)



may do what you need. Another more classical and portable solution is to use
a correlated sub-query that makes a COUNT over a virtual table that have
been using a DISTINCT.


Hoping it may help,
Vanderghast, Access MVP