agregate function in subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Dick said:
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
Click to expand...

right here:
GROUP BY invcimpt.[ae#]) AS A
Click to expand...

a GROUP BY is not allowed in a FROM clause ... you probably meant to include
it in the subquery ...?
 
Dick Minter said:
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.
Click to expand...
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
 
Bob Barrows said:
Dick said:
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
Click to expand...

right here:
GROUP BY invcimpt.[ae#]) AS A
Click to expand...

a GROUP BY is not allowed in a FROM clause ... you probably meant to
include it in the subquery ...?
Click to expand...
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
 
Gary said:
Bob Barrows said:
Dick said:
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
Click to expand...

right here:
GROUP BY invcimpt.[ae#]) AS A
Click to expand...

a GROUP BY is not allowed in a FROM clause ... you probably meant to
include it in the subquery ...?
Click to expand...
Click to expand...
Apologies if I am mistaken,
Click to expand...
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 ...
 
Dick said:
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.
Click to expand...
What version of Access?
What are the datatypes of the fields involved so we can try to reproduce
this?
 
Dick said:
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.
Click to expand...
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
 
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

Bob Barrows said:
Dick said:
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.
Click to expand...
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"
Click to expand...
 
Folks,

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

DM

Dick Minter said:
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

Bob Barrows said:
Dick said:
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.
Click to expand...
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"
Click to expand...
Click to expand...
 
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
 
Back
Top