1

I am working with a scheduling database for events.

I am selecting from series of days and INNER JOINING them into the schedules parent "event". In my selection I want to make a condition where the selection has to be older than the CURDATE. Here is what I have tried but am getting an "improper use of grouping error"

SELECT MIN(event_schedule.event_day) as mindate, MAX(event_schedule.event_day) as maxdate, event.event_title  
FROM event_schedule 
INNER JOIN event ON event_schedule.event_id = event.id 
WHERE MAX(event_schedule.event_day) < CURDATE()
GROUP BY event.id
silversunhunter
  • 1,219
  • 2
  • 12
  • 32
  • So add a `GROUP BY event.event_title`. All columns that are not the result of an aggregate expression (`SUM()`, `MAX()`, `MIN()`, `AVG()`, etc.) have to be listed in a `GROUP BY` clause. – Ken White Apr 19 '15 at 01:26

1 Answers1

3

If you change your query to be the following, you should be fine:

SELECT MIN(event_schedule.event_day) as mindate, MAX(event_schedule.event_day) as maxdate, event.event_title  
FROM event_schedule 
INNER JOIN event ON event_schedule.event_id = event.id
GROUP BY event.event_title
HAVING maxdate < CURRENT_DATE()

I removed your WHERE clause in favor of a HAVING clause and added a GROUP BY clause. The GROUP BY is necessary for the query to run correctly, and the reason you are getting the error is because you have an aggregate function in your WHERE clause. The HAVING is like WHERE in that it is a filter (they are very different) but if you want to filter on an aggregated value, such as MAX, you need to do that in the HAVING.

Jacob Lambert
  • 7,449
  • 8
  • 27
  • 47
  • I have never used Having! Cool! Can I combine a where and a having clause... BTW you are right about the grouping, my actual query has a group but I removed thinking it would be simpler for the question. – silversunhunter Apr 19 '15 at 01:49
  • 1
    You can use a `WHERE` and a `HAVING` in the same statement. So if you only wanted to see certain `event.event_title`'s, you would add the `WHERE event.event_title IN (....)` before the `GROUP BY`. – Jacob Lambert Apr 19 '15 at 01:51
  • 2
    Correct. The order of statements goes: `SELECT`->`FROM`->`WHERE`->`GROUP BY`->`HAVING`->`ORDER BY` – Jacob Lambert Apr 19 '15 at 01:56