Can You Group By With A Case When Then Alias Name?

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

Solution 1:

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

SELECTCASEWHEN DATEDIFF(o.EndDate, o.StartDate) < 30THEN'<1 Month'WHEN DATEDIFF(o.EndDate, o.StartDate) < 90THEN'1 - 2 Months'WHEN DATEDIFF(o.EndDate, o.StartDate) < 210THEN'3 - 4 Months'ELSE'>4 Months' ENDAS `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 = 2AND o.bID = 1GROUPBYCASEWHEN DATEDIFF(o.EndDate, o.StartDate) < 30THEN'<1 Month'WHEN DATEDIFF(o.EndDate, o.StartDate) < 90THEN'1 - 2 Months'WHEN DATEDIFF(o.EndDate, o.StartDate) < 210THEN'3 - 4 Months'ELSE'>4 Months' ENDORDERBY Length ASC;

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

Solution 2:

I was struggling with exactly the same problem and here is the solution I came up with:

SELECTCASEWHEN DATEDIFF(o.EndDate, o.StartDate) < 30THEN'<1 Month'WHEN DATEDIFF(o.EndDate, o.StartDate) < 90THEN'1 - 2 Months'WHEN DATEDIFF(o.EndDate, o.StartDate) < 210THEN'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 = 2AND o.bID = 1GROUPBY `Length`
ORDERBY `Length` ASC;

Solution 3:

You can group-by on statements from select by referencing them like this. See also this question.

SELECTCASEWHEN DATEDIFF(o.EndDate, o.StartDate) <30THEN'<1 Month'WHEN DATEDIFF(o.EndDate, o.StartDate) <90THEN'1 - 2 Months'WHEN DATEDIFF(o.EndDate, o.StartDate) <210THEN'3 - 4 Months'ELSE'>4 Months'ENDAS'Length', 
    COUNT(DISTINCT(person.ID)) AS'COUNT'FROM person
    INNERJOIN opportunity AS o
    INNERJOIN Organization AS org
    ON person.EntityID = o.id 
        AND O.OrganizationID = Org.ID
WHERE person.TitleID =2AND o.bID =1GROUPBY1ORDERBY1ASC;