Subquery group by

  • Thread starter Thread starter ram
  • Start date Start date
R

ram

HI I wouldlike help with the following question. I recieve the can't group by
in the where clause error in the following subquery. Can someone tell me
where i should have the group by. I tried to create a second select statement
but it didn't work for me.

Thanks in advance for any help

SELECT qryProduction.[AgentID],
Sum(nz([qryProduction]![MTDPoints])+nz([Production]![PolicyCount])) AS Expr1,
(Select [EndingProduction]
From qryProduction as Prod2
Where Max([Prod2].[Snap Month Number]) = Max([qryProduction].[Snap Month
Number]))
AS MaxSnapMonth
FROM qryProduction
GROUP BY qryProduction.[AgentID];
 
UNTESTED UNTESTED UNTESTED
Try this --
SELECT qryProduction.[AgentID], Sum(nz([qryProduction].[MTDPoints])
+nz([qryProduction].[PolicyCount])) AS Expr1, (Select TOP 1[EndingProduction]
From qryProduction as Prod2 Where [Prod2].[Snap Month Number] =
[qryProduction].[Snap Month Number] ORDER BY [Prod2].[Snap Month Number]
DESC) AS MaxSnapMonth, Max([qryProduction].[Snap Month Number]) AS
MaxOfSnapMonthNumber
FROM qryProduction
GROUP BY qryProduction.[AgentID],
Sum(nz([qryProduction].[MTDPoints])+nz([qryProduction].[PolicyCount])),
(Select TOP 1[EndingProduction]
From qryProduction as Prod2 Where [Prod2].[Snap Month Number] =
[qryProduction].[Snap Month Number] ORDER BY [Prod2].[Snap Month Number]
DESC);
 
I don't know if that would be enough to make it works, but AT LEAST, it has
to be HAVING, not WHERE. The subquery could also be something like (SELECT
MAX( EndingProduction) FROM ... ) since, otherwise, it MAY tries to
return more that a single row, and generate an error... but even that, it
is quite unusual, at least, to me.



I would have used two queries:

SELECT [AgentID],
SUM(nz([MTDPoints])+nz([PolicyCount])) AS Expr1,
MAX([Snap Month Number]) AS MaxSnapMonth
FROM qryProduction
GROUP BY [AgentID];


as q1, which explicitly return its own MAX to be compared next, and then


SELECT q1.AgentID, LAST(q1.Expr1), MAX(q2.EndingProduction)
FROM q1 INNER JOIN qryProduction AS q2
ON q1.MaxSnapMonth = q2.[Snap Month Number]
GROUP BY q1.AgentID


as final query.

But that may not be what you want. It is just what I assumed to be your
intention.



Vanderghast, Access MVP
 
I wasn't able to run the query with the code shown. I still would like to
Thank you for your suggestion

KARL DEWEY said:
UNTESTED UNTESTED UNTESTED
Try this --
SELECT qryProduction.[AgentID], Sum(nz([qryProduction].[MTDPoints])
+nz([qryProduction].[PolicyCount])) AS Expr1, (Select TOP 1[EndingProduction]
From qryProduction as Prod2 Where [Prod2].[Snap Month Number] =
[qryProduction].[Snap Month Number] ORDER BY [Prod2].[Snap Month Number]
DESC) AS MaxSnapMonth, Max([qryProduction].[Snap Month Number]) AS
MaxOfSnapMonthNumber
FROM qryProduction
GROUP BY qryProduction.[AgentID],
Sum(nz([qryProduction].[MTDPoints])+nz([qryProduction].[PolicyCount])),
(Select TOP 1[EndingProduction]
From qryProduction as Prod2 Where [Prod2].[Snap Month Number] =
[qryProduction].[Snap Month Number] ORDER BY [Prod2].[Snap Month Number]
DESC);

--
Build a little, test a little.


ram said:
HI I wouldlike help with the following question. I recieve the can't group by
in the where clause error in the following subquery. Can someone tell me
where i should have the group by. I tried to create a second select statement
but it didn't work for me.

Thanks in advance for any help

SELECT qryProduction.[AgentID],
Sum(nz([qryProduction]![MTDPoints])+nz([Production]![PolicyCount])) AS Expr1,
(Select [EndingProduction]
From qryProduction as Prod2
Where Max([Prod2].[Snap Month Number]) = Max([qryProduction].[Snap Month
Number]))
AS MaxSnapMonth
FROM qryProduction
GROUP BY qryProduction.[AgentID];
Click to expand...
Click to expand...
 
I used your suggestion and it worked

Thanks

vanderghast said:
I don't know if that would be enough to make it works, but AT LEAST, it has
to be HAVING, not WHERE. The subquery could also be something like (SELECT
MAX( EndingProduction) FROM ... ) since, otherwise, it MAY tries to
return more that a single row, and generate an error... but even that, it
is quite unusual, at least, to me.



I would have used two queries:

SELECT [AgentID],
SUM(nz([MTDPoints])+nz([PolicyCount])) AS Expr1,
MAX([Snap Month Number]) AS MaxSnapMonth
FROM qryProduction
GROUP BY [AgentID];


as q1, which explicitly return its own MAX to be compared next, and then


SELECT q1.AgentID, LAST(q1.Expr1), MAX(q2.EndingProduction)
FROM q1 INNER JOIN qryProduction AS q2
ON q1.MaxSnapMonth = q2.[Snap Month Number]
GROUP BY q1.AgentID


as final query.

But that may not be what you want. It is just what I assumed to be your
intention.



Vanderghast, Access MVP



ram said:
HI I wouldlike help with the following question. I recieve the can't group
by
in the where clause error in the following subquery. Can someone tell me
where i should have the group by. I tried to create a second select
statement
but it didn't work for me.

Thanks in advance for any help

SELECT qryProduction.[AgentID],
Sum(nz([qryProduction]![MTDPoints])+nz([Production]![PolicyCount])) AS
Expr1,
(Select [EndingProduction]
From qryProduction as Prod2
Where Max([Prod2].[Snap Month Number]) = Max([qryProduction].[Snap Month
Number]))
AS MaxSnapMonth
FROM qryProduction
GROUP BY qryProduction.[AgentID];
Click to expand...
Click to expand...
 
Back
Top