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..

It isn't right cause enclosed with wrong quotes. Use backticks instead — Alma Do, Sep 30 '13 at 14:12

score 61 · Accepted Answer · answered Sep 30 '13 at 14:11

61

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.

answered Sep 30 '13 at 14:11

John Woo

258,903
69
498
492

Are you able to ORDER BY 'Length' using something like... ORDER BY FIELD('Length', '<1 Month', '1 - 2 Months', '3 - 4 Months', '>4 Months') – Ken Sep 30 '13 at 14:15
1

@Phil when you wrap something with single quotes, it because a literal string - *not an identifier*. and using `ALIAS` in `ORDER BY` clause is fine because the in the `Order of SQL Operator` `ORDER BY` is the last to be executed. – John Woo Sep 30 '13 at 14:17
You're awesome. Though my ORDER BY FIELD('Length',...) isn't working at all – Ken Sep 30 '13 at 14:19
remove the single quotes around the column. `ORDER BY FIELD(Length,...)` – John Woo Sep 30 '13 at 14:21
In my case there is lot of case statements so I would not like to recalculate the value of DATEDIFF(o.EndDate, o.StartDate), is there any way to keep it in a variable like `@diff:=DATEDIFF(o.EndDate, o.StartDate)` then use @diff for every rows. – PiyusG May 22 '15 at 11:42

score 1 · Answer 2 · answered Apr 28 '17 at 10:32

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

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;

score 1 · Answer 3 · answered Jun 22 '21 at 14:52

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

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 1
ORDER BY 1 ASC;

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

3 Answers3

Linked

Related