asked on 7/21/2008

You tried to execute a query that does not include the specified expression 'Left(ID,InStr(ID&'-','-')-1)' as part of an aggregate function.

Hi,

Extension of question from https://www.experts-exchange.com/questions/23580695/SQL-SELECT-with-Filtering.html

Now I have a table:

Num ID
---------------
1 123
2 123
3 123-X
4 888-X
5 333
6 333-X

I got the error when execute the SQL command.

You tried to execute a query that does not include the specified expression 'Left(ID,InStr(ID&'-','-')-1)' as part of an aggregate function.

How can I resolve it?

SELECT Left(ID,InStr(ID&'-','-')-1) AS N, MIN(Num) AS minrec FROM info GROUP BY info.N ORDER BY info.N

Open in new window

Databases Programming Microsoft Access

Last Comment

harfang

7/23/2008

harfang

7/21/2008

Sadly, you cannot use an aliased expression in the GROUP BY clause. You need to repeat the expression:

SELECT Left(ID,InStr(ID&'-','-')-1) AS N, MIN(Num) AS minrec
FROM info
GROUP BY Left(ID,InStr(ID&'-','-')-1)

However, in Access, the GROUP BY also performs a sort, so you can skip the ORDER BY clause in this case.

(°v°)

cukcuk

7/21/2008

ASKER

Does that mean a full expression of an alias (if there is any) must be used for all GROUP BY statement?

What about for ORDER BY, HAVING, etc..?

BTW, by default the GROUP BY is sorted in ASC order? What if I would like it to be in DESC order?

Thanks for the prompt reply!

ASKER CERTIFIED SOLUTION

harfang

7/21/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

cukcuk

7/23/2008

ASKER

Thanks harfang. Val() is great, however it's interesting to know about how other approach can solve it as well.

Thanks for the help & explaination!

harfang

7/23/2008

You are right. You can the impression that Val() extracts the leading digits, but that is wrong because '12e3' is a valid number. In your particular case, however, it works fine.

Success with your project!
(°v°)

You tried to execute a query that does not include the specified expression 'Left(ID,InStr(ID&amp;'-','-')-1)' as part of an aggregate function.

You tried to execute a query that does not include the specified expression 'Left(ID,InStr(ID&'-','-')-1)' as part of an aggregate function.