GROUP BY and ORDER BY

[Jay]

I'm slowly getting to grips with SQL by forcing myself to write all my
queries diret in sql view.

One thing keeps cropping up. I keep getting a message 'You tried to execute
a query that does not include the specified expression 'tblName.FieldName'
as part of an aggregate function.'

This seems to be when I try to ORDER BY a field that I haven't GROUPed BY.

Do all ORDER BY fields have to be included in the GROUP BY clause?

Apologies if this is a stupid question,

Many thanks,

Jason

 

[Allen Browne]

In a Totals query, Access exeuctes the WHERE clause first (to collect the
data it needs), then the GROUP BY (to aggregate those values), then then
HAVING clause (to eliminate aggregated values you don't want.)

Finally the results are sorted the way you want (the ORDER BY clause.)
Therefore, you can only ORDER BY fields that the query actually outputs. If
a field is only used in selecting data (e.g. in the WHERE clause) but not in
the final output, you cannot ORDER BY that field.

Similarly, there are only very limited things you can do with fields that
are expressions (i.e. results that are not part of the ORDER BY clause.)

 

[Michel Walsh]

If you use an aggregate (MIN, MAX, SUM, COUNT, ...) or if you use GROUP BY
clause, each terms in the SELECT ***and*** in the ORDER BY (and in the
HAVING clause) has to be either aggregated, either in the GROUP BY list,
either be an arithmetic expression combining any members of the previous two
types.

id, cap
1 5
1 7
1 2
2 6

with


SELECT id, cap
FROM table
GROUP BY id
ORDER BY cap


what will be the value of 'cap' we will use to determine the rank of id=1?

Sure, if you already have one and only one record for each group, as with
the data:


id, cap
1 7
2 6


you should 'think' it just has to use the unique value associated to it, but
that is an exception case that SQL does not acknowledge. In that case,
consider:

SELECT id, LAST(cap)
SELECT table
GROUP BY id
ORDER BY LAST(cap)


as example.


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

In an aggregate query you can only order by aggregated data. That means the
field must be in the Group By clause or it must use one of the aggregate
functions in the Select clause (and then you order by the aggregate
function). If you don't do that then the field does not exist and cannot be
used to do an order by.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[David Cox]

Think about it.

If you are trying to order by thingmy, and N different groups have thingmy =
3 and X groups have a thingmy = 5 and some groups have both values, what do
you want the query to do?

 

[Chris2]

I'm slowly getting to grips with SQL by forcing myself to write all my
queries diret in sql view.

Jason,

Congratulations! There is no better way to do this.

One thing keeps cropping up. I keep getting a message 'You tried to execute
a query that does not include the specified expression 'tblName.FieldName'
as part of an aggregate function.'

Whenever there is a GROUP BY clause, every non-aggregate column that appears on the SELECT
clause must appear on the GROUP BY clause.

This seems to be when I try to ORDER BY a field that I haven't GROUPed BY.

Do all ORDER BY fields have to be included in the GROUP BY clause?

I did not think the ORDER BY clause had anything to do with that error message.


Sincerely,

Chris O.