group by errors
From: chris (anonymous.chris_at_mercury-projects.co.nz.discussions.microsoft.com)
Date: 04/27/04
- Next message: Ken Snell: "Re: SQL restructured incorrectly by MS Access Extra"
- Previous message: John Spencer (MVP): "Re: Query keeps returning blank records"
- In reply to: Sean: "group by errors"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 26 Apr 2004 18:11:25 -0700
My guess would be that you are trying to use SQL syntax in
Access which supports a different dialect. Try either
Using a passthrough query in Access or
Creating a view in SQL and use Access to open the view
>-----Original Message-----
>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
>.
>
- Next message: Ken Snell: "Re: SQL restructured incorrectly by MS Access Extra"
- Previous message: John Spencer (MVP): "Re: Query keeps returning blank records"
- In reply to: Sean: "group by errors"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
