Cannot have aggregate function in WHERE clause

M

Mike C

I have the following code:

SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
WHERE X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID;

My data source called "vwShipments" is itself a query and lists orders for
each day for each account, so there are several rows for each account in
vwShipments.

There are several more columns to the second query I'm using, but I'm only
including this one since it's the trouble spot. I'm getting an error that
says: "Cannot have aggregate function in WHERE clause X.INVDATE =
MIN(INVDATE) AND X.[ACCID] = vwShipments.ACCID"

I'm grouping on the account number (ACCID) in vwShipments and then trying to
use the code above to show me how many units were ordered for each account on
their first order.

Any thoughts would be great! Thanks!
 
P

Per Larsen

Mike said:
I have the following code:

SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
WHERE X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID;

My data source called "vwShipments" is itself a query and lists orders for
each day for each account, so there are several rows for each account in
vwShipments.

There are several more columns to the second query I'm using, but I'm only
including this one since it's the trouble spot. I'm getting an error that
says: "Cannot have aggregate function in WHERE clause X.INVDATE =
MIN(INVDATE) AND X.[ACCID] = vwShipments.ACCID"

I'm grouping on the account number (ACCID) in vwShipments and then trying to
use the code above to show me how many units were ordered for each account on
their first order.

Any thoughts would be great! Thanks!
Click to expand...

Lookup the HAVING clause of the SELECT statement. Might be something like:

SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
GROUP BY X.ShipUnits
HAVING X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID

Hth
PerL
 
M

Mike C

Thanks for the response, but I'm getting the same error.

Per Larsen said:
Mike said:
I have the following code:

SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
WHERE X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID;

My data source called "vwShipments" is itself a query and lists orders for
each day for each account, so there are several rows for each account in
vwShipments.

There are several more columns to the second query I'm using, but I'm only
including this one since it's the trouble spot. I'm getting an error that
says: "Cannot have aggregate function in WHERE clause X.INVDATE =
MIN(INVDATE) AND X.[ACCID] = vwShipments.ACCID"

I'm grouping on the account number (ACCID) in vwShipments and then trying to
use the code above to show me how many units were ordered for each account on
their first order.

Any thoughts would be great! Thanks!
Click to expand...

Lookup the HAVING clause of the SELECT statement. Might be something like:

SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
GROUP BY X.ShipUnits
HAVING X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID

Hth
PerL
Click to expand...
 
K

Ken Snell \(MVP\)

Probably need to nest subqueries:


SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
WHERE X.INVDATE =
(SELECT MIN(VW.INVDATE) FROM vwShipments AS VW)
AND X.[ACCID] =
vwShipments.ACCID) AS [Initial Order Units]
FROM vwShipments
GROUP BY vwShipments.ACCID
ORDER BY vwShipments.ACCID;
 
Top