Group By Alias name

Question

I have the following T-SQL query:

select 
    count(CaseId),
    (SELECT DATEDIFF(day,CreateDate,LastActivityDate)) AS DiffDate
from 
    VW_Case_Analysis
where 
    CaseStatus = 'C' 
    and LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
Group By 
    DiffDate

I am getting the following error:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'DiffDate'.

The idea behind this query is that I want to get number of cases solved (closed) within how many days.

Example:

Days

1 = 3 cases

2 = 50 cases

3 = 20 cases

how can I achieve this?

Answer 1

You cannot use alias in the group by clause.

Either use a derived table or in your case simple remove the 2nd SELECT.

select 
    count(CaseId),
    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
from 
    VW_Case_Analysis
where 
    CaseStatus = 'C' 
    and LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
Group By 
    DATEDIFF(day,CreateDate,LastActivityDate)

Answer 2

You need to used the whole expression in the GROUP BY clause or just wrap the whole statement in a subquery an do the grouping on the outer statement.

The reason why you can't use ALIAS on the GROUP BY clause that is created on the same level of the SELECT statement is because the GROUP BY is executed before the SELECT clause in which the ALIAS is created.

This is the SQL Order of Operation:

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

Try this query,

SELECT  COUNT(CaseId),
        DiffDate
FROM
        (
            select  CaseId,
                    DATEDIFF(day,CreateDate,LastActivityDate) AS DiffDate
            from    VW_Case_Analysis
            where   CaseStatus = 'C' and 
                    LastActivityDate between '2013-4-1 00:00:00.000' and '2013-4-30 23:59:59.000'
        ) sub
Group By DiffDate