Alias for expression results within SELECT statements

What are the ways I could make something like this work?

Select Expression1 as 'Column1', Expression2 as 'Column2', Column1 + Column2 as 'Column3'
From table1
Inner join Table2
Group By Criteria1, Criteria2

Where Expression1 and Expression2 are complex expressions.

Exactly the way you have it. (And another variation I'll demonstrate.)

Code Snippet

Select
Expression1 as 'Column1',
Expression2 as 'Column2',
Expression1 + Expression2 as 'Column3'
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Expression 'should' be in parentheses -but not totally necessary.

My preference is this:

Code Snippet

Select
Column1 = ( Expression1 ),
Column2 = ( Expression2 ),
Column3 = ( Expression1 + Expression2 )
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

The expression values must be constants, functions(), static values, and data fields from the tables in the query. You cannot use the aliased column names in the expressions since they will not have been materialized yet when called.

|||

You can use a derived table, a view, or a CTE if you are using 2005.

select Column1, Column2, Column1 + Column2 as Columns3

from (

-- here put the initial query

...

) as t

AMB

|||

Code Snippet

Select
Expression1 as 'Column1',
Expression2 as 'Column2',
Expression1 + Expression2 as 'Column3'
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Expression 'should' be in parentheses -but not totally necessary.

My preference is this:

Code Snippet

Select
Column1 = ( Expression1 ),
Column2 = ( Expression2 ),
Column3 = ( Expression1 + Expression2 )
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Neither of these work for me, I get 'Invalid Column Name xxx' error. My expression is a Sum(Case When Then End). Thoughts on how I could make it work in this case?
|||

Of course it is not going to work as presented above. There is no column named 'Expression1' or 'Expression2' in the table.

You need to plug in your expression, real data, and column names BEFORE anything will work.

If you need more detailed help, then you will have to provide more detailed information for us to work with. We can't give you what we don't have.

Please post the entire query as you 'think' it should be, as well as the table DDL, and some sample data in the form of INSERT statements.

|||

Code Snippet

Select IsNull(DepartmentDetails.DepartmentName, 'Total') as 'Division', #tempResourceAllocation.ProjectCategory,
[RequestsStartOfPeriod] = Sum(Case When Condition1 Then 1 End),

[NewRequests] = Sum(Case When Condition2 Then 1 End),
[Completed] = Sum(Case When Condition3 Then 1 End),
[NotRequired] = Sum(Case When Condition4 Then 1 End),

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])

From #tempResourceAllocation
Right join
(
Subquery here
) DepartmentDetails
On (DepartmentDetails.ProjectCategory = #tempResourceAllocation.ProjectCategory)
Group By #tempResourceAllocation.ProjectCategory, DepartmentDetails.DepartmentName With Rollup

I think there has been a misunderstanding I didn't just copy your example and attempt running it, I just modified my existing query to reflect the method. That's how I got the error. Here's the structure of my query.
|||

Try using a derived table or just re-use the formulas.

select

[Division],

[ProjectCategory],

[RequestsStartOfPeriod],

[NewRequests],

[Completed],

[NotRequired],

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])
from

(

Select IsNull(DepartmentDetails.DepartmentName, 'Total') as 'Division', #tempResourceAllocation.ProjectCategory,
[RequestsStartOfPeriod] = Sum(Case When Condition1 Then 1 End),

[NewRequests] = Sum(Case When Condition2 Then 1 End),
[Completed] = Sum(Case When Condition3 Then 1 End),
[NotRequired] = Sum(Case When Condition4 Then 1 End)

From #tempResourceAllocation
Right join
(
Subquery here
) DepartmentDetails
On (DepartmentDetails.ProjectCategory = #tempResourceAllocation.ProjectCategory)
Group By #tempResourceAllocation.ProjectCategory, DepartmentDetails.DepartmentName With Rollup

) as t

go

AMB

|||

I still can't see what the Conditions might be. I hope that they are in the form of

WHEN {value} {operator} {value} THEN {value}

WHEN x = y THEN 1.

I feel like I'm working in the dark here. Trying to diagnose something that i can't see...

Please post the entire query, complete error messages, the table DDL, and some sample data in the form of INSERT statements.

And this statement cannot work -you are referring to the expressions by their alias name but they cannot be accessed by the alias name in the scope of the same query -except in the ORDER BY.

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])

Perhaps you need to explore one or more derived tables to create the results of your 'complex expressions' so that you don't have to keep re-typing them -which is what you would have to do since you cannot use the alias.

|||My conditions are correct, the problem is with using the alias names for the columns. I knew this wouldn't work which is why I posted the question in the first place, but then I misunderstood your first reply to mean that it was indeed possible to use those aliases. I'm new at this so I second guess myself easily.

I will use a derived table, it's not a big deal.

Thanks for the input.