wish there was a CountDistinct() function

H

h2fcell

I have to create a query that counts the number of DISTINCT Agencies that
contribute to the sum of GrossSales grouped by Division.

The follow query returns the correct sum of GrossSales by Division, but
counts the number of contributing orders instead of DISTINCT contributing
Agencies.

SELECT qryStatisticalAgencySummary.Division,
Sum(qryStatisticalAgencySummary.Total) AS SumOfTotal,
Count(qryStatisticalAgencySummary.customer_id) AS CountOfcustomer_id
FROM qryStatisticalAgencySummary
GROUP BY qryStatisticalAgencySummary.Division;

Example: A Table with 20 orders each for $50 dollars, from 6 different
Agencies each pertaining to one of 3 Divisions should return something like
the below.

Division SumOfTotal Agencies
Division1, $300, 2
Division2, $450, 3
Division3, $250, 1

Instead my query returns:

Division SumOfTotal Agencies
Division1, $300, 6
Division2, $450, 9
Division3, $250, 5

I wish there was a CountDistinct() function. Any help to make this query
work is appreciated.
 
J

Jeff Boyce

No guarantees ... have you tried modifying the query in SQL view, to look
like:

SELECT DISTINCT qryStatistical...


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
J

John W. Vinson

I have to create a query that counts the number of DISTINCT Agencies that
contribute to the sum of GrossSales grouped by Division.

The follow query returns the correct sum of GrossSales by Division, but
counts the number of contributing orders instead of DISTINCT contributing
Agencies.

SELECT qryStatisticalAgencySummary.Division,
Sum(qryStatisticalAgencySummary.Total) AS SumOfTotal,
Count(qryStatisticalAgencySummary.customer_id) AS CountOfcustomer_id
FROM qryStatisticalAgencySummary
GROUP BY qryStatisticalAgencySummary.Division;

Example: A Table with 20 orders each for $50 dollars, from 6 different
Agencies each pertaining to one of 3 Divisions should return something like
the below.

Division SumOfTotal Agencies
Division1, $300, 2
Division2, $450, 3
Division3, $250, 1

Instead my query returns:

Division SumOfTotal Agencies
Division1, $300, 6
Division2, $450, 9
Division3, $250, 5

I wish there was a CountDistinct() function. Any help to make this query
work is appreciated.
Click to expand...

You can use a Subquery; replace

Count(qryStatisticalAgencySummary.customer_id) AS CountOfcustomer_id

with

(SELECT Count(*) FROM (SELECT DISTINCT Customer_ID FROM
qryStatisticalAgencySummary))

or you might be able to "drill down" closer to the tables rather than having
so many nested queries. Not knowing your table structure I can't say how...
 
H

h2fcell

Hi John,
Thanks for the reply. I tried your suggestion
(SELECT Count(*) FROM (SELECT DISTINCT Customer_ID FROM
qryStatisticalAgencySummary))

but the out put was:

Division SumOfTotal Agencies
Division1, $300, 6
Division2, $450, 6
Division3, $250, 6
 
B

Bob Barrows

You will probably need to group and correlate by division, like this:

SELECT Division,Count(*) FROM (SELECT DISTINCT Division,Customer_ID FROM
qryStatisticalAgencySummary) as q
group by division
 
J

John Spencer

Couple of choices.
This gets asked reasonably often, so here is a new article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

OR try to modify your query to use the following as a calculated column:
Count(Select Distinct CustomerID FROM qryStatisticalAgencySummary as Temp
WHERE Temp.Division = qryStatisticalAgencySummary.Division) as CountCustomer

SELECT qryStatisticalAgencySummary.Division,
Sum(qryStatisticalAgencySummary.Total) AS SumOfTotal
, Count(Select Distinct CustomerID FROM qryStatisticalAgencySummary as Temp
WHERE Temp.Division = qryStatisticalAgencySummary.Division) as CountCustomer
FROM qryStatisticalAgencySummary
GROUP BY qryStatisticalAgencySummary.Division;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top