title: SQL GROUP BY CASE
body: <p>I am trying to get the age of visitor from three tables:
  Visits that holds the viist date by client ID, and
  Clients that holds the Client ID, and
  Clinic that holds the Clinic ID.</p>
  
  <p>Here is my sql statement:</p>
  
  <pre><code>DECLARE @Clinicname nvarchar(50) 
  SET @Clinicname='First Clinic'
  
  SELECT CASE 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 10 THEN '1-10' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 20 THEN '11-20' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 30 THEN '21-30'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 40 THEN '31-40' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 50 THEN '41-50'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 60 THEN '51-60'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 70 THEN '61-70'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 80 THEN '71-80'
           ELSE '81+' 
         END AS age, 
         COUNT(*) AS n
  
  FROM  Visit v 
            INNER JOIN  Client c ON v.ClientID = c.ClientID
            INNER JOIN  Clinic r ON v.ClinicId = r.ClinicId
            WHERE r.Name IN (@Clinicname)   
  GROUP BY CASE 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 10 THEN '1-10' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 20 THEN '11-20' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 30 THEN '21-30'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 40 THEN '31-40' 
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 50 THEN '41-50'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 60 THEN '51-60'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 70 THEN '61-70'
           WHEN DATEDIFF(YEAR, c.BirthDate, MIN(v.Date)) &lt;= 80 THEN '71-80'
           ELSE '81+'
           END 
  </code></pre>
  
  <p>Here is the error:
  Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.</p>
  
  <p>When I replace MIN(v.Date) with GETDATE() it works.</p>