Word “Distinct “ using problem.

[Dawn]

Table Account, Field customerid, AccountNo (PK), AccountType(for example:
Saving account; Current account, Qdii, etc )
Rule: One customerid may open several accounts , each with a unique
AccountNo, and each has an AccountType. One customerid can open several
accounts of the same AccountType, but the AccountNos are different.
Job description:
Calculate the count of customerids(No duplication) under the classification
of AccountType.
Problem:
Because the customerid are not unique, the result of the query is
duplicating. Of course ,the job can be done by writing two queries, one for
generating a detailed data pool according to the condition, the other quoting
the first query for generating the collected results.
But if I want to do this job in one query, how to realize it? (for example,
using Word “Distinct “)
Thanks.
Dawn

 

[Allen Browne]

So for each account type, you want to know how many distinct customers?

Create a query based on your AccountType table. In the FROM clause, use a
subquery that returns the unique customers.

You can try this example in the Northwind database. It returns the unique
clients who have bought each product:

SELECT Products.ProductID,
Products.ProductName,
Q.CustomerID
FROM Products INNER JOIN
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
AS Q ON Products.ProductID = Q.ProductID;

If you just want the count, you can collapse it like this:

SELECT Products.ProductID,
Products.ProductName,
Count(Q.CustomerID) AS NumCustomers
FROM Products INNER JOIN
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
AS Q ON Products.ProductID = Q.ProductID
GROUP BY Products.ProductID, Products.ProductName;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Dawn]

thanks!

So for each account type, you want to know how many distinct customers?

Create a query based on your AccountType table. In the FROM clause, use a
subquery that returns the unique customers.

You can try this example in the Northwind database. It returns the unique
clients who have bought each product:

SELECT Products.ProductID,
Products.ProductName,
Q.CustomerID
FROM Products INNER JOIN
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
AS Q ON Products.ProductID = Q.ProductID;

If you just want the count, you can collapse it like this:

SELECT Products.ProductID,
Products.ProductName,
Count(Q.CustomerID) AS NumCustomers
FROM Products INNER JOIN
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
AS Q ON Products.ProductID = Q.ProductID
GROUP BY Products.ProductID, Products.ProductName;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Table Account, Field customerid, AccountNo (PK), AccountType(for example:
Saving account; Current account, Qdii, etc )
Rule: One customerid may open several accounts , each with a unique
AccountNo, and each has an AccountType. One customerid can open several
accounts of the same AccountType, but the AccountNos are different.
Job description:
Calculate the count of customerids(No duplication) under the
classification
of AccountType.
Problem:
Because the customerid are not unique, the result of the query is
duplicating. Of course ,the job can be done by writing two queries, one
for
generating a detailed data pool according to the condition, the other
quoting
the first query for generating the collected results.
But if I want to do this job in one query, how to realize it? (for
example,
using Word “Distinct “)
Thanks.
Dawn

 

[Bob Barrows [MVP]]

Table Account, Field customerid, AccountNo (PK), AccountType(for
example: Saving account; Current account, Qdii, etc )
Rule: One customerid may open several accounts , each with a unique
AccountNo, and each has an AccountType. One customerid can open
several accounts of the same AccountType, but the AccountNos are
different.
Job description:
Calculate the count of customerids(No duplication) under the
classification of AccountType.
Problem:
Because the customerid are not unique, the result of the query is
duplicating. Of course ,the job can be done by writing two queries,
one for generating a detailed data pool according to the condition,
the other quoting the first query for generating the collected
results.
But if I want to do this job in one query, how to realize it? (for
example, using Word "Distinct ")
Thanks.
Dawn

In SQL Server, it would be possible using COUNT(Distinct customerid).
Unfortunately for you, Jet does not support that syntax, so you will need to
go with your two-query plan. I'm not sure why you don't want to simply save
the query to generate the unique customerids per account type, but you can
do it in one query by using a subquery:

select AccountType, Count(*) as CustomerCount
FROM (SELECT DISTINCT AccountType,
customerid FROM Account) As q
Group By AccountType