Prev: RelationShips Queries and joins
Next: subtotal in access?
From: Krzysztof Naworyta on 14 May 2010 09:41
Krzysztof Naworyta wrote:

|| It isn't really clear what you would expect to return in the Count(X)
|| as Years.
|
| You can get any aggregate value from pivotet columns, by using its
| alias:
|
| TRANSFORM Avg(water_level) AS x
| SELECT
| well
| , count(x) as c1
| , avg(x) as a1
| , sum(x) as s1
|
| FROM MyTable
| GROUP BY well
| PIVOT Year([visit_date]);

Columns c1, a1, s1 are equal to c2,a2,s2:

SELECT
well
, Count(x) AS c2
, Avg(x) AS a2
, Sum(x) AS s2

FROM
(
SELECT
well
, Year([date1]) AS y
, Avg(water_level) AS x
FROM MyTable
GROUP BY
well
, Year([date1])
) tmp
GROUP BY
well;

--
KN
From: vanderghast on 14 May 2010 10:36
Indeed, if you aggregate on the alias of the TRANSFORMed expression, the
result is "as if" you would have aggregated HORIZONTALLY in the crosstab.

Here, a given row, a given well, may not have a value for each PIVOT
YEAR(visit), and for those "cells", a null is supplied and then, the
horizontal aggregate COUNT on these columns does not count the year where
there is no data, returning the number of year with some data, for the given
well. So you get a DISTINCT COUNT.

It is a short cut which allows, in ONE query, to aggregate over aggregate (a
vertical aggregate and next, an horizontal one over the vertical aggregated
just done, assuming you are in a visual representation).

You can even remove the generated fields from the result by adding an
IN(NULL) to the PIVOT expression., and you will be left with the
'horizontal' final aggregate.

(From initial observations reported by Steve Dassin, in my best knowledge).


Vanderghast, Access MVP

First  |  Prev  | 
Pages: 1 2
Prev: RelationShips Queries and joins
Next: subtotal in access?