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 festi开发者_JS百科vals WHERE min(datefrom) > now() 

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

don't use MIN...

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

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.