Cannot have aggregate function in WHERE clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 19 Jul 2006 23:42 GMT

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!

Reply to this Message

Per Larsen - 20 Jul 2006 01:21 GMT

> I have the following code:
>
[quoted text clipped - 19 lines]
>
> 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

Reply to this Message

Mike C - 20 Jul 2006 02:20 GMT

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

> > I have the following code:
> >
[quoted text clipped - 32 lines]
> Hth
> PerL

Reply to this Message

Ken Snell (MVP) - 20 Jul 2006 03:14 GMT

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;

Signature

Ken Snell
<MS ACCESS MVP>

>I have the following code:
>
[quoted text clipped - 21 lines]
>
> Any thoughts would be great! Thanks!

Reply to this Message

Jamie Collins - 20 Jul 2006 15:52 GMT

> SELECT vwShipments.ACCID, (SELECT X.ShipUnits FROM vwShipments AS X
> WHERE X.INVDATE = MIN(INVDATE) AND X.[ACCID] =
[quoted text clipped - 6 lines]
> 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.

--

Reply to this Message