开发者

Access 2003 - count unique values

How to count unique values in Access 2003? When i write sth like this:

SELECT DISTINCT Custo开发者_JAVA技巧mer FROM CustomersTable;

I've got as result unique customers, but how count them if this code doesn't work:

SELECT COUNT(*) 

FROM (SELECT DISTINCT Customer FROM CustomersTable )

(it causes error: "The Microsoft Jet database engine cannot find the input table or query 'SELECT DISTINCT Customer FROM CustomersTable'. Make sure it exists and that its name is spelled correctly")

Example of database

--Customer -- Address -- 
X               NY 
X               OR 
Y               AR 
Z               WA 

And I'd like to have as result 3 (three unique Customers.)


You need to give your subquery a name:

SELECT COUNT(*) 
  FROM (SELECT DISTINCT Customer FROM CustomersTable) AS T


Try this:

Select 
    Customer, 
    count(Customer) as count
from
    CustomersTable
group by
    Customer

Alright, You should try this now:

SELECT COUNT(DISTINCT Customer) FROM CustomersTable;

Hope this helps.


Edit (Query Corrected)

This worked for me in Access 2007

SELECT Count(*) AS NumCustomers
FROM 
(
    SELECT Distinct CustomerAddress.Customer
    FROM CustomerAddress
)  AS DistinctCustomers;


I know this question was probably abandoned a long time ago but just for anyone who is having this issue, as I was having errors using a C# application to do a similar task and found refuge here: http://www.geeksengine.com/article/access-distinct-count.html

This helped me a lot and if broken, gave the following query structure

    SELECT COUNT(columnName) as num_ofColumnNameEntries
    from
       (
        SELECT DISTINCT columnName FROM TableName
       )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜