Crosstab column data count

[tmb]

I have a crosstab query with Well Name as row heading , site visit Dates as
column headings (formatted as "yyyy"), and averaged water level measurements
data as the values. The column headings range from 2004-2010 and some wells
have no measurements during some of these years.

I need to create a dataset from the crosstab that shows total number of
years for which each well has data. My attempts at changing the column
headings and values around to accomplish this have been futile. Please help.

 

[KARL DEWEY]

Post your crosstab SQL.

 

[Krzysztof Naworyta]

tmb wrote:
| I have a crosstab query with Well Name as row heading , site visit
| Dates as column headings (formatted as "yyyy"), and averaged water
| level measurements data as the values. The column headings range from
| 2004-2010 and some wells have no measurements during some of these
| years.
|
| I need to create a dataset from the crosstab that shows total number
| of years for which each well has data. My attempts at changing the
| column headings and values around to accomplish this have been
| futile. Please help.

TRANSFORM Avg(water_level) AS x
SELECT well, count(x) as years
FROM MyTable
GROUP BY well
PIVOT Year([visit_date]);

 

[Duane Hookom]

It isn't really clear what you would expect to return in the Count(X) as
Years. You could create a totals query that groups by Well and Year. Then
create another totals query that groups by Well and counts the number of
years. Add this to your crosstab and join the Well columns and group by the
CountOfYears.

 

[Krzysztof Naworyta]

Duane Hookom 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)
, avg(x)
, sum(x)

FROM MyTable
GROUP BY well
PIVOT Year([visit_date]);

You can see that it is different than:

TRANSFORM Avg(water_level) AS x
SELECT
well
, count(water_level)
, avg(water_level)
, sum(water_level)

FROM MyTable
GROUP BY well
PIVOT Year([visit_date]);

 

[Krzysztof Naworyta]

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;

 

[vanderghast]

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