SELECT AS problem

Question

I tried to perform the following query:

SELECT t1.[user1], t1.[user2],
    (CAST(t1.[total_event_duration] AS DECIMAL)) / (CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength 
FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 
INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 
    ON t1.[user1] = t2.[user1]  
WHERE buddy_strength > 0.02

But it returns an error "Invalid column name 'buddy_strength'"

Does anyone know how to fix the query above?

Answer 1

SELECT * 
FROM
    (
    SELECT
        t1.[user1], t1.[user2],(CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)) AS buddy_strength 
        FROM [CDRs].[dbo].[aggregate_monthly_events] AS t1 
            INNER JOIN [CDRs].[dbo].[user_monthly_stats] AS t2 
                ON t1.[user1] = t2.[user1]  


   ) foo
        WHERE foo.buddy_strength > 0.02

Answer 2

You cannot use aliases in WHERE clause. You need to repeat the whole expression (CAST(t1.[total_event_duration] AS DECIMAL))/(CAST (t2.[total_events_duration] AS DECIMAL)>0.02).