S
Sean
Hello all,
I'm using Access 2k as a front end to a SQL server. I'm trying to
create some queries to aggregate some data. Looking at the help files,
I see group all by ... with cube | rollup as being valid.
I get syntax errors if I try group all by, group by with cube, or
group by with rollup. Does anyone know what is going on? Is the help
file incorrect?
Syntax
[ GROUP BY [ALL] group_by_expression [,…n]
[ WITH { CUBE | ROLLUP } ]
]
Arguments
ALL
Includes all groups and result sets, even those that do not have any
rows that meet the search condition specified in the WHERE clause.
When ALL is specified, null values are returned for the summary
columns of groups that do not meet the search condition. You cannot
specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables.
group_by_expression
Is an expression on which grouping is performed. group_by_expression
is also known as a grouping column. group_by expression can be a
column or a nonaggregate expression that references a column. A column
alias that is defined in the select list cannot be used to specify a
grouping column. When GROUP BY is specified, any column in the select
list (except a column used in an aggregate function) must be specified
as a grouping column.
--------------------------------------------------------------------------------
Note Columns of type text, ntext, image, and bit cannot be used in
group_by_expression.
--------------------------------------------------------------------------------
For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of
group_by_expression items is limited by the GROUP BY column sizes, the
aggregated columns, and the aggregate values involved in the query.
This limit originates from the limit of 8,060 bytes on the
intermediate work table that is needed to hold intermediate query
results. A maximum of 10 grouping expressions is permitted when CUBE
or ROLLUP is specified.
CUBE
Specifies that, in addition to the usual rows provided by GROUP BY,
summary rows are introduced into the result set. A GROUP BY summary
row is returned for every possible combination of group and subgroup
in the result set. A GROUP BY summary row is displayed as NULL in the
result, but is used to indicate all values. Use the GROUPING function
to determine whether null values in the result set are GROUP BY
summary values.
The number of summary rows in the result set is determined by the
number of columns included in the GROUP BY clause. Each operand
(column) in the GROUP BY clause is bound under the grouping NULL and
grouping is applied to all other operands (columns). Because CUBE
returns every possible combination of group and subgroup, the number
of rows is the same, regardless of the order in which the grouping
columns are specified.
ROLLUP
Specifies that, in addition to the usual rows provided by GROUP BY,
summary rows are introduced into the result set. Groups are summarized
in a hierarchical order, from the lowest level in the group to the
highest. The group hierarchy is determined by the order in which the
grouping columns are specified. Changing the order of the grouping
columns can affect the number of rows produced in the result set.
--------------------------------------------------------------------------------
Important Distinct aggregates, for example, AVG(DISTINCT column_name),
COUNT(DISTINCT column_name), and SUM(DISTINCT column_name), are not
supported when using CUBE or ROLLUP. If used, SQL Server returns an
error message and cancels the query.
Thanks
Sean
I'm using Access 2k as a front end to a SQL server. I'm trying to
create some queries to aggregate some data. Looking at the help files,
I see group all by ... with cube | rollup as being valid.
I get syntax errors if I try group all by, group by with cube, or
group by with rollup. Does anyone know what is going on? Is the help
file incorrect?
Syntax
[ GROUP BY [ALL] group_by_expression [,…n]
[ WITH { CUBE | ROLLUP } ]
]
Arguments
ALL
Includes all groups and result sets, even those that do not have any
rows that meet the search condition specified in the WHERE clause.
When ALL is specified, null values are returned for the summary
columns of groups that do not meet the search condition. You cannot
specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables.
group_by_expression
Is an expression on which grouping is performed. group_by_expression
is also known as a grouping column. group_by expression can be a
column or a nonaggregate expression that references a column. A column
alias that is defined in the select list cannot be used to specify a
grouping column. When GROUP BY is specified, any column in the select
list (except a column used in an aggregate function) must be specified
as a grouping column.
--------------------------------------------------------------------------------
Note Columns of type text, ntext, image, and bit cannot be used in
group_by_expression.
--------------------------------------------------------------------------------
For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of
group_by_expression items is limited by the GROUP BY column sizes, the
aggregated columns, and the aggregate values involved in the query.
This limit originates from the limit of 8,060 bytes on the
intermediate work table that is needed to hold intermediate query
results. A maximum of 10 grouping expressions is permitted when CUBE
or ROLLUP is specified.
CUBE
Specifies that, in addition to the usual rows provided by GROUP BY,
summary rows are introduced into the result set. A GROUP BY summary
row is returned for every possible combination of group and subgroup
in the result set. A GROUP BY summary row is displayed as NULL in the
result, but is used to indicate all values. Use the GROUPING function
to determine whether null values in the result set are GROUP BY
summary values.
The number of summary rows in the result set is determined by the
number of columns included in the GROUP BY clause. Each operand
(column) in the GROUP BY clause is bound under the grouping NULL and
grouping is applied to all other operands (columns). Because CUBE
returns every possible combination of group and subgroup, the number
of rows is the same, regardless of the order in which the grouping
columns are specified.
ROLLUP
Specifies that, in addition to the usual rows provided by GROUP BY,
summary rows are introduced into the result set. Groups are summarized
in a hierarchical order, from the lowest level in the group to the
highest. The group hierarchy is determined by the order in which the
grouping columns are specified. Changing the order of the grouping
columns can affect the number of rows produced in the result set.
--------------------------------------------------------------------------------
Important Distinct aggregates, for example, AVG(DISTINCT column_name),
COUNT(DISTINCT column_name), and SUM(DISTINCT column_name), are not
supported when using CUBE or ROLLUP. If used, SQL Server returns an
error message and cancels the query.
Thanks
Sean