Distinct Count in Crosstab Query

[kittyfool via AccessMonster.com]

Hi All,

how can i count the distinct value of my crosstab query?

I need to count the distinct cust id by each period.

however, the SQL below will show all the cust id in each month.

i only need to count the unique ID. Can any 1 help me change my SQL to show
only the distinct count of Cust ID by each period.

TRANSFORM Count(tblDPM.[Cust ID]) AS [CountOfCust ID]
SELECT [SG RM LIST].[RM Consolidated]
FROM (tblDPM INNER JOIN [SG RM LIST] ON tblDPM.[RM Code] = [SG RM LIST].[RM
Code]) INNER JOIN [PRODUCT HIERARCHY] ON tblDPM.[Product Description] =
[PRODUCT HIERARCHY].[Product Description]
GROUP BY [SG RM LIST].[RM Consolidated]
PIVOT tblDPM.Period;


Any help is appreciated.

 

[kittyfool via AccessMonster.com]

Any kind soul can help me?
I m going in circles.

Hi All,

how can i count the distinct value of my crosstab query?

I need to count the distinct cust id by each period.

however, the SQL below will show all the cust id in each month.

i only need to count the unique ID. Can any 1 help me change my SQL to show
only the distinct count of Cust ID by each period.

TRANSFORM Count(tblDPM.[Cust ID]) AS [CountOfCust ID]
SELECT [SG RM LIST].[RM Consolidated]
FROM (tblDPM INNER JOIN [SG RM LIST] ON tblDPM.[RM Code] = [SG RM LIST].[RM
Code]) INNER JOIN [PRODUCT HIERARCHY] ON tblDPM.[Product Description] =
[PRODUCT HIERARCHY].[Product Description]
GROUP BY [SG RM LIST].[RM Consolidated]
PIVOT tblDPM.Period;

Any help is appreciated.

 

[Gary Walter]

Classic Dassin NorthWind example demonstrating
that once you give the TRANSFORM value
an alias, i.e. "cnt," that alias can be used in further
aggregation within the group:

TRANSFORM Count(*) AS cnt
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt
FROM Orders
GROUP BY
Orders.ShipCountry
PIVOT Orders.EmployeeID;

splitting one record for "Argentina":

Ship Country TotalOrders DistinctCnt
Argentina 16 8

AvgCnt MaxCnt MinCnt 1 2 3 4 5 6 7 8 9

2 4 1 1 1 1 4 1 3 3 2

the "DistinctCnt" counts the number of
EmployeeID's that had a value, i.e.,
emp 5 had no value for Argentina,
but other 8 did. Is that what you mean
by "distinct count?"

 

[Gary Walter]

just to be sure we are on same page
for what "Distinct Count" might mean....

when the crosstab is looking at the
group for "Argentina," the Expression

Count(cnt) AS DistinctCnt

is the same as if it were running the
following:

SELECT Count(*) AS DistinctCnt
FROM
[SELECT
DISTINCT EmployeeID
FROM
Orders
WHERE [ShipCountry]="Argentina"]. AS Q;

Classic Dassin-like NorthWind example demonstrating
that once you give the TRANSFORM value
an alias, i.e. "cnt," that alias can be used in further
aggregation within the group:

TRANSFORM Count(*) AS cnt
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt
FROM Orders
GROUP BY
Orders.ShipCountry
PIVOT Orders.EmployeeID;

splitting one record for "Argentina":

Ship Country TotalOrders DistinctCnt
Argentina 16 8

AvgCnt MaxCnt MinCnt 1 2 3 4 5 6 7 8 9

2 4 1 1 1 1 4 1 3 3 2

the "DistinctCnt" counts the number of
EmployeeID's that had a value, i.e.,
emp 5 had no value for Argentina,
but other 8 did. Is that what you mean
by "distinct count?"

how can i count the distinct value of my crosstab query?

I need to count the distinct cust id by each period.

however, the SQL below will show all the cust id in each month.

i only need to count the unique ID. Can any 1 help me change my SQL to
show
only the distinct count of Cust ID by each period.

TRANSFORM Count(tblDPM.[Cust ID]) AS [CountOfCust ID]
SELECT [SG RM LIST].[RM Consolidated]
FROM (tblDPM INNER JOIN [SG RM LIST] ON tblDPM.[RM Code] = [SG RM
LIST].[RM
Code]) INNER JOIN [PRODUCT HIERARCHY] ON tblDPM.[Product Description] =
[PRODUCT HIERARCHY].[Product Description]
GROUP BY [SG RM LIST].[RM Consolidated]
PIVOT tblDPM.Period;


Any help is appreciated.

 

[Gary Walter]

of course, the query parser will not
allow the unnecessary brackets around
ShipCountry....so technically....

SELECT Count(*) AS DistinctCnt
FROM
[SELECT
DISTINCT EmployeeID
FROM
Orders
WHERE ShipCountry = "Argentina"]. AS Q;

 

[Gary Walter]

if you also wanted to count the *unique values*
of the counts, one way would be to save a
query like:

qryUniqueValuesCntPerShipCountry:

SELECT
Q.ShipCountry,
Count(*) AS UniqueValuesCnt
FROM
[SELECT DISTINCT O.ShipCountry, Count(*) AS EmpCntryCnt
FROM Orders AS O
GROUP BY O.ShipCountry, O.EmployeeID]. AS Q
GROUP BY Q.ShipCountry;

then join this query to original xtab table

TRANSFORM Count(*) AS cnt
SELECT
O.ShipCountry,
q.UniqueValuesCnt,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt,
StDev([cnt]) AS StandDev,
Var([cnt]) AS Variance
FROM
Orders AS O
INNER JOIN
qryUniqueValuesCntPerShipCountry AS q
ON
O.ShipCountry = q.ShipCountry
GROUP BY
O.ShipCountry,
q.UniqueValuesCnt
PIVOT O.EmployeeID;

just to be sure we are on same page
for what "Distinct Count" might mean....

when the crosstab is looking at the
group for "Argentina," the Expression

Count(cnt) AS DistinctCnt

is the same as if it were running the
following:

SELECT Count(*) AS DistinctCnt
FROM
[SELECT
DISTINCT EmployeeID
FROM
Orders
WHERE [ShipCountry]="Argentina"]. AS Q;

Classic Dassin-like NorthWind example demonstrating
that once you give the TRANSFORM value
an alias, i.e. "cnt," that alias can be used in further
aggregation within the group:

TRANSFORM Count(*) AS cnt
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt
FROM Orders
GROUP BY
Orders.ShipCountry
PIVOT Orders.EmployeeID;

splitting one record for "Argentina":

Ship Country TotalOrders DistinctCnt
Argentina 16 8

AvgCnt MaxCnt MinCnt 1 2 3 4 5 6 7 8 9

2 4 1 1 1 1 4 1 3 3 2

the "DistinctCnt" counts the number of
EmployeeID's that had a value, i.e.,
emp 5 had no value for Argentina,
but other 8 did. Is that what you mean
by "distinct count?"

how can i count the distinct value of my crosstab query?

I need to count the distinct cust id by each period.

however, the SQL below will show all the cust id in each month.

i only need to count the unique ID. Can any 1 help me change my SQL to
show
only the distinct count of Cust ID by each period.

TRANSFORM Count(tblDPM.[Cust ID]) AS [CountOfCust ID]
SELECT [SG RM LIST].[RM Consolidated]
FROM (tblDPM INNER JOIN [SG RM LIST] ON tblDPM.[RM Code] = [SG RM
LIST].[RM
Code]) INNER JOIN [PRODUCT HIERARCHY] ON tblDPM.[Product Description] =
[PRODUCT HIERARCHY].[Product Description]
GROUP BY [SG RM LIST].[RM Consolidated]
PIVOT tblDPM.Period;


Any help is appreciated.

 

[kittyfool via AccessMonster.com]

hi gary,

How can i relate your example to my database?

TRANSFORM Count(DPM.[Cust Name]) AS [CountOfCust Name]
SELECT DPM.[RM Consolidated]
FROM DPM
GROUP BY DPM.[RM Consolidated]
PIVOT DPM.Period;

I need to count each unique number of cust id in each period from the
following crosstab. However the crosstab now show count of all the cust id
that appear in that particular period.

Do you mind if you can help me change my SQL statement?

if you also wanted to count the *unique values*
of the counts, one way would be to save a
query like:

qryUniqueValuesCntPerShipCountry:

SELECT
Q.ShipCountry,
Count(*) AS UniqueValuesCnt
FROM
[SELECT DISTINCT O.ShipCountry, Count(*) AS EmpCntryCnt
FROM Orders AS O
GROUP BY O.ShipCountry, O.EmployeeID]. AS Q
GROUP BY Q.ShipCountry;

then join this query to original xtab table

TRANSFORM Count(*) AS cnt
SELECT
O.ShipCountry,
q.UniqueValuesCnt,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt,
StDev([cnt]) AS StandDev,
Var([cnt]) AS Variance
FROM
Orders AS O
INNER JOIN
qryUniqueValuesCntPerShipCountry AS q
ON
O.ShipCountry = q.ShipCountry
GROUP BY
O.ShipCountry,
q.UniqueValuesCnt
PIVOT O.EmployeeID;

just to be sure we are on same page
for what "Distinct Count" might mean....

[quoted text clipped - 68 lines]

 

[Gary Walter]

I apologise for answering when I was not
sure what you were asking. Many times
one provides some sample data in the
posts and then the results one expects
from a query operating on the sample
data.

I don't know your data so that was why
I gave you examples where the data was
something we both know......

But, let's try and think this through...

in your recent xtab you basically have a group of

Period
[RM Consolidated]

and for each group there will be a certain number
of records (a count)

and even though you have used

Count([Cust Name])

you really are only doing

Count(*)

if no [Cust Name] is Null in the group.

in your xtab, the number that falls under
a Period is the count of non-Null [Cust Name]'s
in a group of

Period
[RM Consolidated]

and if there were no [Cust Name]'s that
were Null, it is just the count of records
in that group. In fact, if you do not expect
a Null [Cust Name], you would be better
off just changing TRANSFORM clause to

TRANSFORM Count(*) As cnt

so you can forget about it ever giving
any distinct count of [Cust Name]'s.

{please respond back if the above
point is not clear}

the point being that if we added an Expression

Count([CountOfCust Name]) As DistinctCnt

that really would be the the count of distinct
*Periods* in each [RM Consolidated] group.

==========================
okay...."divide and conquer"

suppose you ran the following query

Q1:

SELECT
[RM Consolidated] As RMC,
Period,
[Cust Name] As CustName
FROM
DPM
GROUP BY
[RM Consolidated],
Period,
[Cust Name];

suppose the silly results

RMC Period CustName
A 2006q1 Homer
A 2006q1 Marge
A 2006q1 Bart
A 2006q2 Lisa
A 2006q2 Maggie

then we ran a totals query on Q1

SELECT
Count(*) As cnt,
RMC,
Period
FROM
Q1
GROUP BY
RMC,
Period;

cnt RMC Period
3 A 2006q1
2 A 2006q2

or, if you wanted the Period's in columns,
and the distinct count under the Period's,
that sounds like a xtab

TRANSFORM Count(*) As DistinctCntOfCust
SELECT
RMC
FROM
Q1
GROUP BY RMC
PIVOT Period;

which I believe would give you

RMC 2006q1 2006q2
A 3 2

If that is what you wanted, please give "Q1"
a meaningful name.

Do you see what we did?

In Q1 we created distinct groups
of RMC, Period, and *CustName*
This allowed us to count distinct CustName's
in a RMC/Period group.

We could have done all of this in one
query but because you have used SPACE
in your field names causing them to need
to be bracketed, that was not possible.

If this is still not what you wanted, you
will need to please provide some sample
data and the result you want from your
query for that sample data.

How can i relate your example to my database?

TRANSFORM Count(DPM.[Cust Name]) AS [CountOfCust Name]
SELECT DPM.[RM Consolidated]
FROM DPM
GROUP BY DPM.[RM Consolidated]
PIVOT DPM.Period;

I need to count each unique number of cust id in each period from the
following crosstab. However the crosstab now show count of all the cust id
that appear in that particular period.

Do you mind if you can help me change my SQL statement?

if you also wanted to count the *unique values*
of the counts, one way would be to save a
query like:

qryUniqueValuesCntPerShipCountry:

SELECT
Q.ShipCountry,
Count(*) AS UniqueValuesCnt
FROM
[SELECT DISTINCT O.ShipCountry, Count(*) AS EmpCntryCnt
FROM Orders AS O
GROUP BY O.ShipCountry, O.EmployeeID]. AS Q
GROUP BY Q.ShipCountry;

then join this query to original xtab table

TRANSFORM Count(*) AS cnt
SELECT
O.ShipCountry,
q.UniqueValuesCnt,
Count(*) AS TotalOrders,
Count(cnt) AS DistinctCnt,
Avg(cnt) AS AvgCnt,
Max(cnt) AS MaxCnt,
Min(cnt) AS MinCnt,
StDev([cnt]) AS StandDev,
Var([cnt]) AS Variance
FROM
Orders AS O
INNER JOIN
qryUniqueValuesCntPerShipCountry AS q
ON
O.ShipCountry = q.ShipCountry
GROUP BY
O.ShipCountry,
q.UniqueValuesCnt
PIVOT O.EmployeeID;

just to be sure we are on same page
for what "Distinct Count" might mean....

[quoted text clipped - 68 lines]

Any help is appreciated.

 

[Gary Walter]

better wording to reflect what
the count represents:

TRANSFORM Count(*) As CntOfUniqueCust
SELECT
RMC
FROM
Q1
GROUP BY RMC
PIVOT Period;

 

[kittyfool via AccessMonster.com]

Hi Gary,

Thanks for your help. The result in the crosstab is what i wanted. Thanks a
million.

 

[kittyfool via AccessMonster.com]

Hi gary,
I need your help on my crosstab query again.
How can i exclude a count if a particular customer has a value of zero (0) in
that particular period?

Thanks

Hi Gary,

Thanks for your help. The result in the crosstab is what i wanted. Thanks a
million.

better wording to reflect what
the count represents:

[quoted text clipped - 6 lines]

GROUP BY RMC
PIVOT Period;

 

[Gary Walter]

I need your help on my crosstab query again.
How can i exclude a count if a particular customer has a value of zero (0)
in
that particular period?

I don't understand.

Can you relate it back to our example data?

Q1:

SELECT
[RM Consolidated] As RMC,
Period,
[Cust Name] As CustName
FROM
DPM
GROUP BY
[RM Consolidated],
Period,
[Cust Name];

suppose results:

RMC Period CustName
A 2006q1 Homer
A 2006q1 Marge
A 2006q1 Bart
A 2006q2 Lisa
A 2006q2 Maggie
B 2006q1 Homer
B 2006q2 Homer

TRANSFORM Count(*) As CntOfUniqueCusts
SELECT
RMC
FROM
Q1
GROUP BY RMC
PIVOT Period;

RMC 2006q1 2006q2
A 3 2
B 1 1

Homer does not have a "value" (record) in Period
of "2006q2" for RMC of "A", but does have a
"value" (record) for RMC of "B" for Period
of "2006q2."

How can i exclude a count if a particular customer has
a value of zero (0) in that particular period?

Do you want result to be?

RMC 2006q1 2006q2
A 3
B 1 1

Please reply back if that is the case,
but I doubt that is what you mean?

====================
Or is it that your "Q1" is also providing
a "value" for each group?

Q1:

SELECT
[RM Consolidated] As RMC,
Period,
[Cust Name] As CustName,
Sum([Sales] - [Returns]) As NetSales
FROM
DPM
GROUP BY
[RM Consolidated],
Period,
[Cust Name];

and supposing results:

RMC Period CustName NetSales
A 2006q1 Homer 0
A 2006q1 Marge 5
A 2006q1 Bart 1
A 2006q2 Lisa 8
A 2006q2 Maggie 1
B 2006q1 Homer 4
B 2006q2 Homer 3

For "A/2006q1" Homer had at least one
sale but had the same returns, so his
"value" of NetSales was zero(0), so
you do not want to include him in the
count for "A/2006q1" ?

If that is the case one method would be to
introduce a HAVING clause in Q1:

Q1:

SELECT
[RM Consolidated] As RMC,
Period,
[Cust Name] As CustName,
Sum([Sales] - [Returns]) As NetSales
FROM
DPM
GROUP BY
[RM Consolidated],
Period,
[Cust Name]
HAVING
([Cust Name] = "Homer"
AND
Sum([Sales] - [Returns]) = 0) = False;

RMC Period CustName NetSales
A 2006q1 Marge 5
A 2006q1 Bart 1
A 2006q2 Lisa 8
A 2006q2 Maggie 1
B 2006q1 Homer 4
B 2006q2 Homer 3

Homer is gone from group "A/2006q1"

so our xtab would produce

RMC 2006q1 2006q2
A 2 2
B 1 1

if you were not concerned with *just* Homer,
but did not want to include *any* [Cust Name]
whose "value" was 0, then alter the Having clause

SELECT
[RM Consolidated] As RMC,
Period,
[Cust Name] As CustName
FROM
DPM
GROUP BY
[RM Consolidated],
Period,
[Cust Name]
HAVING
Sum([Sales] - [Returns]) > 0;

which would return

RMC Period CustName
A 2006q1 Bart
A 2006q1 Marge
A 2006q2 Lisa
A 2006q2 Maggie
B 2006q1 Homer
B 2006q2 Homer

again, Homer is gone from group "A/2006q1"
but, if any others had "value" <= 0, they would
be gone also...

and xtab

TRANSFORM Count(*) As CntOfUniqueCusts
SELECT
RMC
FROM
Q1
GROUP BY RMC
PIVOT Period;

would still produce (since only Homer "value" of 0)

RMC 2006q1 2006q2
A 2 2
B 1 1

====================
quick review of designing Q1 in grid....

when you change a select query to a "group by"
(by clicking on Sigma icon in toolbar), you get
an extra row ("Total:") in grid

Field: CustName: [Cust Name]
Table: DPM
Total: Group By
Sort:
Show:
Criteria:
or:

Besides "Group By," this row can be any of
the aggregate functions (like Sum), plus it can
also be either

Where
Expression

an Expression can use any previous alias
(like "CustName" above) if you do not
apply a Criteria to it.

Once you apply a Criteria to an expression,
that expression gets added to the HAVING
clause, and the alias is "out-of-scope" in both
the WHERE and HAVING clauses.

{WHERE filters before the grouping,
HAVING filters after the groupings
have been done}

If we had tried the following
(using both aliases "CustName" and "Ignore")

Field: Ignore: [CustName] = "Homer"
Table: DPM
Total: Expression
Sort:
Show: <checked>
Criteria:
or:

Access will be happy...
[Ignore] will show up as field in the
query results with value of -1 (true)
or 0 (false).

But, if we wanted to filter out the groups by
writing out in SQL

HAVING [Ignore] = True

or

([CustName] = "Homer") = True

or, in grid,

Field: Ignore: [CustName] = "Homer"
Table: DPM
Total: Expression
Sort:
Show: <checked>
Criteria: True
or:

Access will ask you what the alias "CustName" is....

So.....in grid, for a field expression that
will have a Criteria, remember to not use
any aliases.....

Field: Ignore: [Cust Name] = "Homer"
Table: DPM
Total: Expression
Sort:
Show:
Criteria: True
or:

or, writing out SQL...

HAVING ([Cust Name] = "Homer") = True

Plus, remember that if you set

Total: Expression

and give that column a Criteria,

it will end up in the HAVING clause.

Sometimes it is more efficient (not in our
specific case here) to filter before the
grouping occurs, so you would want

Total: Where