crm crm - 1 year ago 118
MySQL Question

MySQL Invalid use of group function with max()

I am trying to select a boolean value where order.lastUpdated is older than 30 minutes ago, but I get the error:

SQL state [HY000]; error code [1111]; Invalid use of group function;
nested exception is java.sql.SQLException: Invalid use of group

Here is the query:

c.externalReference channelReference, channelId,
max(o.lastUpdated) lastUpdated,
sum(if(max(o.lastUpdated) < date_sub(now(), interval 30 minute), 0, 1)) beforeThreshold
from channel c
join order_item o on = o.channelId
where date(o.lastUpdated) = date(now())
and o.lastUpdated > date_sub(now(), interval 1 hour)
group by c.externalReference;

How can I return a boolean value if
is older than 30 minutes ago where I have to use

Answer Source

You can't nest aggregation functions like SUM() and MAX(). You need to do the inner one in a subquery.

SELECT c.externalReference AS channelReference, AS ChannelId
       SUM(IF(o.lastUpdated < DATE_SUB(NOW(), INTERVAL 30 MINUTE), 0, 1) AS beforeThreshold
FROM channel AS c
JOIN (SELECT channelId, MAX(lastUpdated) AS lastUpdated
      FROM order_item
      WHERE DATE(lastUpdated) = TODAY()
        AND lastUpdated > DATE_SUB(NOW(), INTERVAL 1 HOUR)
      GROUP BY channelId) AS o
ON = o.channelId
GROUP BY c.externalReference