CODEWITHSUNDEEP
sqlsql-server
Yousi
Yousi

Reputation: 1143

Group By Alias name

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?

Upvotes: 11

Views: 30870

Answers (5)

Ricardo
Ricardo

Reputation: 808

You can use CROSS APPLY to create an alias and use it in the GROUP BY clause, like so:

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

Upvotes: 0

user4848354
user4848354

Reputation: 1

this is a easy example of Implementation Discussion

select selectResualt.num ,count(selectResualt.num) as count  
from (select src_num As num from mytable  UNION ALL select dis_num As num from mytable) as selectResualt
group by selectResualt.num
order by count(selectResualt.num) DESC;>

Upvotes: 0

DK91
DK91

Reputation: 1

Wouldn't this get you the same results?

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)

I am a novice at SQL so maybe I am not grasping it correctly. I saw the comment above mine with the exact syntax but I didn't know if it was right...

Upvotes: 0

Ahmed
Ahmed

Reputation: 101

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)

Upvotes: 3

John Woo
John Woo

Reputation: 263703

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

Upvotes: 21

Related Questions