473,903 Members | 4,560 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jul 20 '05 #1
6 72692
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
--
Jul 20 '05 #2
>> 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
Jul 20 '05 #3
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

Jul 20 '05 #4
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.
Jul 20 '05 #5
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"



Jul 20 '05 #6
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
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
6059
by: Andrew | last post by:
With command-line interface ( 3.23.37, UNIX Socket ) all is well with column aliasing. However, column aliases disappear in Excel, over ODBC, when there are multiple (joined) tables in the query. I run the following query with aliased columns with CLI: select table1.c1 as 'pet category', table1.c2 as 'item', table1.c3 as 'quantity', table2.c2 as 'attendant' from table1 left outer join table2 on (table1.c1=table2.c1);...
1
32362
by: Caroline | last post by:
I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct query? 2 - I tried copying them record by record, but the datatype is ntext, (it displays <long
3
2214
by: uw_badgers | last post by:
Is it possible to create a unique constraint to a column from another table? For example: tb_current: current_names -------------- aaa bbb tb_new:
2
9260
by: sanderson82 | last post by:
Hi I am having problems with the HAVING clause. I know it can use aliases but the alias I am using has a space in it, eg 'Device ID' My querry looks like (simplified, devID is a calculation) SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123' and it does not work
2
3483
by: sks | last post by:
Hi all, Is possible to retrieve all columns and alias them all at once. Eg, normally you would write select * from products which would return id | name | price ----------------------- 1 | Test | 14.00
1
6870
by: Dougeth | last post by:
Hi All, I have having a problem conducting an aggregated function off an alias column I create on the fly. I get an error saying SearchType is an invalid column name yet when I remove the Count and Group By commands the query results in a column with the name of SearchType. SELECT Count(RFQ_ID), "SearchType"= CASE WHEN SVC_COLO = 'Y' THEN 'Colocation' WHEN SVC_BANDWIDTH = 'Y' THEN 'Bandwidth' WHEN SVC_MANAGED = 'Y' THEN...
1
2663
by: c8tz | last post by:
This is a query I have created that picks up the top 3 dates for this data (for example) : Tree Pos2 Date 1 15 23/08/2005 1 20 12/02/2006 1 32 15/10/2006 2 17 23/08/2005 2 18 12/02/2006
1
2404
by: Plamen Ratchev | last post by:
You cannot use a column alias in the definition of another column. The solution is as you found to repeat the expression. Alternative solution is to define the first column in one CTE (or derived table) and then use the column in a second CTE. Also, it is good to define a valid column name (not a revered keyword) and then change it to whatever you want in the final SELECT (or client application).
12
5925
by: sheppardpg | last post by:
I have a a query with a column that needs to have a data type of Memo, which I am able to achive with this function; Public Function MEMO() MEMO (COMMENTS) End Function I want to define the value in this field instead of it pulling the data from the table with the column COMMENTS. In the query I have assigned the column alias and expression as follows; COMMENTS:"". Now the Memo data type no longer works because I'm using the alias. ...
0
10873
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10983
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10500
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8048
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7206
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4726
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3324
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.