use column alias in another calculation

[DC]

Is there a way to use a column alias in an another calculation within the
same query? Since I am using some long and complex logic to compute total1
and total2, I don't want to repeat the same logic to compute the ratio of
those two columns. I know that I can do a nested query, but that seems too
lengthy as well since I actually have many, many columns.

select
total1 = sum(case(long complex logic)),
total2 = sum(case(anothe r long complex logic)),
ratio = total1/total2

[David Portas]

SELECT total1, total2, ratio = total1/total2
FROM
(SELECT
total1 = SUM(CASE(long complex logic)),
total2 = SUM(CASE(anothe r long complex logic))
FROM YourTable) AS T

--
David Portas
SQL Server MVP
--

[Anith Sen]

>> Is there a way to use a column alias in an another calculation within the

same query?

No, you will have to reuse the expression in the calculation or use a
derived table construct like:

SELECT total1, total2, total1/total2 AS "ratio"
FROM ( SELECT SUM ( ... )
SUM ( ... )
FROM ... ) D ( total1, total2 ) ;

--
Anith

[Mike Hodgson]

You could put the code in the first query into a view and then query that
view to get the results of the second query. Eg.

create view dbo.View1
as
select
total1 = sum(case(long complex logic)),
total2 = sum(case(anothe r long complex logic))
go

select total1, total2, (total1/total2) as [ratio] from dbo.View1

Alternately, you could put the results of the first query into a temporary
table and then query the temp table to get the ratio. Or, rather than using
a temp table, you could use a TABLE variable in your batch (see table data
type in SQL BOL). I don't believe you can use column aliases in other
calculations within the same scope with SQL2000. Yukon introduces CTEs
(common table expressions) which will allow you to do basically the same as
above (with the views) except without creating intermediate DB objects
(there's much more to CTEs but that's a whole other thread & a half).

--
Cheers,
Mike

"DC" <no*****@fakead dress.com> wrote in message
news:co******** **@news01.intel .com...

Is there a way to use a column alias in an another calculation within the
same query? Since I am using some long and complex logic to compute total1
and total2, I don't want to repeat the same logic to compute the ratio of
those two columns. I know that I can do a nested query, but that seems too
lengthy as well since I actually have many, many columns.

select
total1 = sum(case(long complex logic)),
total2 = sum(case(anothe r long complex logic)),
ratio = total1/total2

[DC]

Thanks for everyone's replies. I guess I'll just have to use the nested
subquery.
--
Disclaimer: This post is solely an individual opinion and does not speak on
behalf of any organization.

[IAPW]

In article <co**********@n ews01.intel.com >, DC <no*****@fakead dress.com> wrote:

Is there a way to use a column alias in an another calculation within the
same query? Since I am using some long and complex logic to compute total1
and total2, I don't want to repeat the same logic to compute the ratio of
those two columns. I know that I can do a nested query, but that seems too
lengthy as well since I actually have many, many columns.

select
total1 = sum(case(long complex logic)),
total2 = sum(case(anothe r long complex logic)),
ratio = total1/total2

For Sql Server 2000 and above, you can use a user-defined function.
--
" We gonna charge, we gonna stomp, we gonna march through the swamp
We gonna mosh through the marsh, take us right through the doors"

[Erland Sommarskog]

DC (no*****@fakead dress.com) writes:

Thanks for everyone's replies. I guess I'll just have to use the nested
subquery.

No, no one recommended you to use a nested subquery. The recommendation
was to use a derived table, which is something different.

A derived table is a logical temp table within the query. It may or may
not be materialized during query computation, that is up to the optimizer.
The optimizer may in fact evalutate the entire query in pass if that is
feasible. This last is important to know, because you can use derived
tables that if they were computed on their own would be prohibitely
expensive, but may run very well in the actual query.

A subquery is a query which appear where you also could have a column
expression:

SELECT C.CustomerID,
OrderCnt = (SELECT COUNT(*)
FROM Orders O
WHERE O.CustomerID = O.OrderId)
FROM Customers

In SQL 2000, such queries often has considerably worse performance than
the corresponding query with a derived table instead.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp