agregate function in subquery

[Dick Minter]

Can someone tell me why this query fails? Access indicates a syntax error in
the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but this
example is just an element in a more complex problem.

Thanks for your help.

DM

 

[Bob Barrows [MVP]]

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt

right here:

GROUP BY invcimpt.[ae#]) AS A

a GROUP BY is not allowed in a FROM clause ... you probably meant to include
it in the subquery ...?

 

[Gary Walter]

Can someone tell me why this query fails? Access indicates a syntax error
in
the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but this
example is just an element in a more complex problem.

Hi Dick,

PMFBI

It is my experience that a subquery
used in the FROM clause can be
"written" as

(SELECT x FROM y) As A

but, expects to save itself as

[SELECT x FROM y]. As A

and does not like any further brackets
within its own bracketing.

Is it too late to change the fieldname
from "ae#" to something that does not
need brackets?

Apologies again for butting in,

gary

 

[Gary Walter]

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt

right here:

GROUP BY invcimpt.[ae#]) AS A

a GROUP BY is not allowed in a FROM clause ... you probably meant to
include it in the subquery ...?

Hi Bob,

I wonder if you might be thinking
of reports choking on subqueries
when one tries to group on them
in the report?

In my test db I tried a trivial
query that appeared to work.

SELECT
A.txtA,
GB.CountryID,
GB.StateSum
FROM A
LEFT JOIN
(SELECT
Address.CountryID,
Sum(Address.StateID) AS StateSum
FROM Address
GROUP BY Address.CountryID) As GB
ON A.fA = GB.CountryID
GROUP BY
A.txtA,
GB.CountryID,
GB.StateSum;

Apologies if I am mistaken,

gary

 

[Bob Barrows [MVP]]

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt

right here:

GROUP BY invcimpt.[ae#]) AS A

a GROUP BY is not allowed in a FROM clause ... you probably meant to
include it in the subquery ...?

Apologies if I am mistaken,

No, you're right. I failed to see the end parenthesis after "GROUP BY
invcimpt.[ae#]"

It somehow blended right into the rest of the text ...

 

[Bob Barrows [MVP]]

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but
this example is just an element in a more complex problem.

What version of Access?
What are the datatypes of the fields involved so we can try to reproduce
this?

 

[Bob Barrows [MVP]]

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but
this example is just an element in a more complex problem.

When I tried to run your query in A2003, I got this error:
"You tried to execute a query that does not include the expression 'COB' as
part of an aggregate function"
at which point I went "DOH! Of course!"

Would you expect this query to run without error?

SELECT AEName, aeno
FROM ae
GROUP BY AEName

No, of course this would cause an error: you have to either include aeno in
the GROUP BY clause, or apply an aggregate function to it:

SELECT AEName, max(aeno)
FROM ae
GROUP BY AEName

You problem query can be fixed using the same technique:
SELECT ae.AEName, Max(A.sumCOB) AS [COB]
FROM ae LEFT JOIN
(SELECT invcimpt.[ae#], sum(invcimpt.aecom) as sumCOB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

You could also eliminate the subquery, but I assume you know this based on
your statement "I know there is a simpler way to do this particular
operation ... "

Now, the only question is: why did you not get the more explicit error
message that I got? Two possibilities come to mind:

1. You are using an earlier version of Access than the one I used
2. You are attempting to execute it via ADO from an external application
without first testing it in the Access environment

Bob Barrows

 

[Dick Minter]

Bob,

You were right on target. I didn't notice that since I had aggregated the
[aecom] field in the subquery, I had no need to group anything. When I
removed the GROUP BY statement from the primary query everthing worked.

Now for the next step. I need to agregate in the subquery because I
actually have two amounts to aggreage and LEFT JOIN, hence the following
statement:

SELECT ae.AEName, ae.GuaranteeEnd, ae.RegDraw, ae.CurPerTalent, ae.Balance,
A.COB, B.COC
FROM ae LEFT JOIN [SELECT invcimpt.aeno, sum(invcimpt.aecom) as COB FROM
invcimpt
LEFT JOIN [SELECT receipts.ae, sum(receipts.adjcom) as COC FROM receipts
GROUP BY receipts.ae]. AS B ON ae.AENo = A.aeno
WHERE (((ae.Status)="x" Or (ae.Status)="O"))

Unfortunately, this is returning a syntax error. I note that ACCESS placed
a period after the bracket defining alias A, and I repeated it in the second
join statement for alias B, but either with or without the period the query
fails. I followed the syntax of another, successful multiple outer join
query in omitting any punctuation or statments between the first LEFT JOIN
statemet and the second.

DM

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but
this example is just an element in a more complex problem.

When I tried to run your query in A2003, I got this error:
"You tried to execute a query that does not include the expression 'COB' as
part of an aggregate function"
at which point I went "DOH! Of course!"

Would you expect this query to run without error?

SELECT AEName, aeno
FROM ae
GROUP BY AEName

No, of course this would cause an error: you have to either include aeno in
the GROUP BY clause, or apply an aggregate function to it:

SELECT AEName, max(aeno)
FROM ae
GROUP BY AEName

You problem query can be fixed using the same technique:
SELECT ae.AEName, Max(A.sumCOB) AS [COB]
FROM ae LEFT JOIN
(SELECT invcimpt.[ae#], sum(invcimpt.aecom) as sumCOB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

You could also eliminate the subquery, but I assume you know this based on
your statement "I know there is a simpler way to do this particular
operation ... "

Now, the only question is: why did you not get the more explicit error
message that I got? Two possibilities come to mind:

1. You are using an earlier version of Access than the one I used
2. You are attempting to execute it via ADO from an external application
without first testing it in the Access environment

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Dick Minter]

Folks,

I got the below SQL statement to work by enclosing the entire first LEFT
JOIN statement in parentheses, but not the second.

DM

Bob,

You were right on target. I didn't notice that since I had aggregated the
[aecom] field in the subquery, I had no need to group anything. When I
removed the GROUP BY statement from the primary query everthing worked.

Now for the next step. I need to agregate in the subquery because I
actually have two amounts to aggreage and LEFT JOIN, hence the following
statement:

SELECT ae.AEName, ae.GuaranteeEnd, ae.RegDraw, ae.CurPerTalent, ae.Balance,
A.COB, B.COC
FROM ae LEFT JOIN [SELECT invcimpt.aeno, sum(invcimpt.aecom) as COB FROM
invcimpt
LEFT JOIN [SELECT receipts.ae, sum(receipts.adjcom) as COC FROM receipts
GROUP BY receipts.ae]. AS B ON ae.AENo = A.aeno
WHERE (((ae.Status)="x" Or (ae.Status)="O"))

Unfortunately, this is returning a syntax error. I note that ACCESS placed
a period after the bracket defining alias A, and I repeated it in the second
join statement for alias B, but either with or without the period the query
fails. I followed the syntax of another, successful multiple outer join
query in omitting any punctuation or statments between the first LEFT JOIN
statemet and the second.

DM

Can someone tell me why this query fails? Access indicates a syntax
error in the FROM clause, but I don't see it.

SELECT ae.AEName, A.COB
FROM ae LEFT JOIN (SELECT invcimpt.[ae#], sum(invcimpt.aecom) as COB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

I know there is a simpler way to do this particular operation, but
this example is just an element in a more complex problem.

When I tried to run your query in A2003, I got this error:
"You tried to execute a query that does not include the expression 'COB' as
part of an aggregate function"
at which point I went "DOH! Of course!"

Would you expect this query to run without error?

SELECT AEName, aeno
FROM ae
GROUP BY AEName

No, of course this would cause an error: you have to either include aeno in
the GROUP BY clause, or apply an aggregate function to it:

SELECT AEName, max(aeno)
FROM ae
GROUP BY AEName

You problem query can be fixed using the same technique:
SELECT ae.AEName, Max(A.sumCOB) AS [COB]
FROM ae LEFT JOIN
(SELECT invcimpt.[ae#], sum(invcimpt.aecom) as sumCOB
FROM invcimpt GROUP BY invcimpt.[ae#]) AS A ON ae.aeno = A.[ae#]
WHERE (((ae.Status)="x" Or (ae.Status)="O"))
GROUP BY ae.AEName;

You could also eliminate the subquery, but I assume you know this based on
your statement "I know there is a simpler way to do this particular
operation ... "

Now, the only question is: why did you not get the more explicit error
message that I got? Two possibilities come to mind:

1. You are using an earlier version of Access than the one I used
2. You are attempting to execute it via ADO from an external application
without first testing it in the Access environment

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Michel Walsh]

Hi,


You cannot SELECT a list of constants, as a sub-query.

You may try

(SELECT cte1, cte2 FROM tableName WHERE somethingTrueFormJustOneRecord)


or

(SELECT DISTINCT cte1, cte2 FROM tableName)


but try to supply a FROM clause.

In the immediate debug window, you can try:


? CurrentProject.Connection.Execute("SELECT 5").Fields(0).Value
5

? CurrentProject.Connection.Execute( "SELECT (SELECT 5 AS y) AS x
" ).Fields(0).Value
---error




Hoping it may help,
Vanderghast, Access MVP