AVG of a Count in a Crosstab-query

  • Thread starter Daan den Hollander via AccessMonster.com
  • Start date
D

Daan den Hollander via AccessMonster.com

I have a crosstabquery that performs a count of the Categorie-field.
I'd like an AVG based on the total of the total-field in the row.
Something like AVG(Count([Tbl_Incidenten.ID))

This is the SQL-statement of the count.

TRANSFORM Count(Tbl_Incidenten.ID) AS CountOfID
SELECT Tbl_Incidenten.Categorie, Count(Tbl_Incidenten.ID) AS
TotalofCategorie
FROM Tbl_Incidenten
GROUP BY Tbl_Incidenten.Categorie
PIVOT Format(Format([Datum],"yyyy")) & "-"+Format(Format([Datum],"mm"),"00")
;

Hope you can help me.
Daan
 
M

MGFoster

Daan said:
I have a crosstabquery that performs a count of the Categorie-field.
I'd like an AVG based on the total of the total-field in the row.
Something like AVG(Count([Tbl_Incidenten.ID))

This is the SQL-statement of the count.

TRANSFORM Count(Tbl_Incidenten.ID) AS CountOfID
SELECT Tbl_Incidenten.Categorie, Count(Tbl_Incidenten.ID) AS
TotalofCategorie
FROM Tbl_Incidenten
GROUP BY Tbl_Incidenten.Categorie
PIVOT Format(Format([Datum],"yyyy")) & "-"+Format(Format([Datum],"mm"),"00")
;
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't believe it can be done in one query since the avg would depend
on the number of columns the crosstab produces, and that isn't known
until the crosstab runs. You could put a criteria in the crosstab that
indicates the column date range the query absolutely, positively will
include. Then something like this:

PARAMETERS [Start Date?] Date, [End Date?] Date;
TRANSFORM Count(ID) AS CountOfID
SELECT Categorie, Count(ID) AS TotalofCategorie,
Count(ID) / (DateDiff("m", [Start Date?], [End Date?])+1) As Average
FROM Tbl_Incidenten
WHERE Datum BETWEEN [Start Date?] And [End Date?]
GROUP BY Categorie
PIVOT Format([Datum],"yyyy-mm"))

If the number of columns differs from the results of the DateDiff()
function then the avg will be wrong.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnKSK4echKqOuFEgEQLAGwCg1hg0JVSbw0Z7sfdrgCcKgow86HQAoKIM
ptMB+NrNvbSD/YAgXlXyBzRe
=jBOo
-----END PGP SIGNATURE-----
 
G

Gary Walter

Hi Dann,

Maybe this example will help....

Example using NorthWind mdb
(from Michel/Steve Dassin)

TRANSFORM COUNT(*) As countCity
SELECT Customers.Country,
COUNT(countCity) As DistinctCount,
COUNT(*) As TotalCount,
AVG(countCity) As AvgCount
FROM Customers
GROUP BY Country
PIVOT city

Country DistinctCount TotalCount AvgCount
Argentina 1 3 3
Austria 2 2 1
Belgium 2 2 1
Brazil 4 9 2.25
Canada 3 3 1
Denmark 2 2 1
Finland 2 2 1
France 9 11 1.22222222222222
Germany 11 11 1
Ireland 1 1 1
Italy 3 3 1
Mexico 1 5 5
Norway 1 1 1
Poland 1 1 1
Portugal 1 2 2
Spain 3 5 1.66666666666667
Sweden 2 2 1
Switzerland 2 2 1
UK 2 7 3.5
USA 12 13 1.08333333333333
Venezuela 4 4 1


there was only one city from Argentina,
but 3 records for that city, so avg = 3

for UK, there were 2 cities
Cowes 1 record
London 6 records
--> (6 + 1) /2 = 3.5

the point is that once you give the pivot column an alias,
you can further use that alias within aggregate functions.

Taken to the extreme, here be an example
from Steve Dassin:


transform count(*) as cnt
select
[shipcountry],
[employeeid] as emp,
avg(cnt) as avg1,
stdev(cnt) as std1,
max(abs(avg1-cnt)) as maxdifftest,
switch(maxdifftest>=std1,maxdifftest,true,0) as maxdiff,
max( switch(maxdiff<>0,
switch(avg1+maxdiff-cnt between 0 and 0.01 or avg1-maxdiff-cnt between 0 and
0.01,cnt),true,-9)) as [cnt for maxdiff],
max(switch(cnt=cint([cnt for maxdiff]),emp)) as [extreme employeeid]
from orders
group by [shipcountry]
pivot [employeeid];

good luck,

gary
 
Top