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
)
精彩评论