Hi,
I'm having trouble with this query:

SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
GROUP BY ofm_baskets.Code
HAVING (((ofm_basklist.Order_id)=0))
ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));

I get the following error:
ERROR 1111: Invalid use of group function

Can someone give me an idea of what's wrong with this query? I'm using MySQLd version 4.0.16.

Thanks,

Eric

May be you need use 2 group parameters?
For example, GROUP BY ofm_baskets.Code, TotalQty

what is the datatype of Lastupdate? what are some sample values in that column?

your GROUP BY is correct assuming that the expression involving Lastupdate is correct

rewrite the HAVING condition as a WHERE condition

Aha!
Currently Lastupdate is varchar(10), but that was not actually the source of the problem. The problem turns out to be attempting to use the grouping function Max in the ORDER BY clause.

After changing the query to:
SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
WHERE (ofm_basklist.Order_id)=0
GROUP BY ofm_baskets.Code
ORDER BY BaskDate;

Now it works perfectly.

I cut my teeth on Access/SQL SERVER queries, and my original query is how Access does ORDER BY clauses, so beware of that if you go from one to the other.

Many thanks for the helpful suggestions.

you're right, and i knew that too! sorry

can't use the aggregate in the ORDER BY in mysql, can't use the alias in sql server