CODEWITHSUNDEEP
mysql
Adam Boyle
Adam Boyle

Reputation: 89

Min, Max, Group By and DateDiff

I'm trying to optimise many MySQL statements into one but have come across a problem.

MySQL query:

  SELECT companyid, COUNT(*) AS total 
    FROM timesheet 
   WHERE userId='1' 
     AND DATEDIFF(MIN(startDate), MAX(endDate)) <= 77 
GROUP BY companyid 
  HAVING total = 11

What I'm trying to do is select all companies where exactly 11 records exist and the first and last records were created within 11 weeks of each other. The error I get is "Invalid use of group function".

This is to do with the MIN(), MAX() and DATEDIFF() part but I can't for the life of me figure it out!

Upvotes: 0

Views: 2195

Answers (1)

heisenberg
heisenberg

Reputation: 9759

Aggregate functions needs to be in the HAVING clause, not the WHERE clause. (eg your DATEDIFF(MIN(startDate), MAX(endDate)) <= 77 condition)

Think of HAVING as applying to grouped records, and WHERE applying to individual records before being grouped.

Upvotes: 2

Related Questions