Cannot have aggregate function in WHERE clause

[Guest]

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!

 

[Per Larsen]

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!

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

 

[Guest]

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

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!

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

 

[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;

 

[Jamie Collins]

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;

I'm getting an error that
says: "Cannot have aggregate function in WHERE clause X.INVDATE =
MIN(INVDATE) AND X.[ACCID] = vwShipments.ACCID"

Try this:

SELECT S1.ACCID, S1.INVDATE, S1.ShipUnits
FROM vwShipments AS S1,
(
SELECT ACCID, MIN(INVDATE) AS min_invdate
FROM vwShipments
GROUP BY ACCID
) AS DT1
WHERE S1.ACCID = DT1.ACCID
AND S1.INVDATE = DT1.min_INVDATE;

Jamie.

--