counting rows that date hasn't yet passed

Question

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() 

Answer 1

don't use MIN...

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

Answer 2

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

Don't use the min function. That selects the minimum date which is not what you are looking for.

The min function is normally used as follows

SELECT MIN(dateFrom) FROM festivals

Answer 3

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.