Alias question

The following is not working:
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>