Can you GROUP BY with a CASE WHEN THEN alias name?

Question

I have a SELECT statement being calculated from a CASE WHEN THEN state (or could use multiple IF statements) aliased as 'Length', and I need to correctly GROUP the results together. The SELECT seems to be working, but the group groups them wrong. Here is my statement:

SELECT CASE      WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'     WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'     WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'     ELSE '>4 Months' END AS 'Length',      COUNT(DISTINCT(person.ID)) AS 'COUNT' FROM person     INNER JOIN opportunity AS o     INNER JOIN Organization AS org     ON person.EntityID = o.id          AND O.OrganizationID = Org.ID WHERE person.TitleID = 2     AND o.bID = 1 GROUP BY 'Length' ORDER BY 'Length' ASC; 

This groups all results into '3 - 4 Months' which isn't right..

Answer 1

You need to use the whole CASE statement in the GROUP BY clause if you don't wrapped it in a subquery.

SELECT  CASE              WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'             WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'             WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'             ELSE '>4 Months'          END AS `Length`,          COUNT(DISTINCT(person.ID)) AS `COUNT` FROM    person         INNER JOIN opportunity AS o             ON person.EntityID = o.id         INNER JOIN Organization AS org             ON o.OrganizationID = Org.ID WHERE   person.TitleID = 2         AND o.bID = 1 GROUP   BY  CASE                  WHEN DATEDIFF(o.EndDate, o.StartDate) < 30 THEN '<1 Month'                 WHEN DATEDIFF(o.EndDate, o.StartDate) < 90 THEN '1 - 2 Months'                 WHEN DATEDIFF(o.EndDate, o.StartDate) < 210 THEN '3 - 4 Months'                 ELSE '>4 Months'              END ORDER   BY Length ASC; 

Remove also the single quotes around the column name in the ORDER BY clause.