Search code examples
sqlmysqlmysql-error-1111

counting rows that date hasn't yet passed

I am trying to count the number of rows whose date has not yet passed so i can get only the current records

I get an error sayng

MySQL error #111 Invalid use of group function 

SELECT COUNT(festivalid) FROM festivals WHERE min(datefrom) > now()
Solution

  • The reason for the error is that you can not use aggregate (IE: MIN, MAX, COUNT...) functions in the WHERE clause - only in the HAVING clause can you do this. And to define the HAVING clause, your query needs to have a GROUP BY clause defined:

      SELECT COUNT(f.festivalid)
    FROM FESTIVALS f
GROUP BY ? --festivalid would NOT be an ideal choice
  HAVING MIN(datefrom) > now()

    ...but I have my doubts about the query, and think it would be better to use:

    SELECT COUNT(f.festivalid) 
  FROM FESTIVALS f
 WHERE f.datefrom > CURRENT_TIMESTAMP

    CURRENT_TIMESTAMP is ANSI standard equivalent to MySQL specific NOW(), making the query portable to other databases.