Column aliases in queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Column aliases in queries

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-12 : 09:26:15

This is my query:


SELECT '007' AS DRIVER,
(SELECT SUM(TOTAL) FROM FILE1 WHERE FeeType = 'ABC')  AS BASEVALUE,
((SELECT COUNT(COL1) FROM TBL1)+(SELECT COUNT(COL1) FROM TBL2)+(SELECT COUNT(COL1) FROM TBL3)) AS COMPAREVALUE,
((SELECT COUNT(COL1) FROM TBL1)+(SELECT COUNT(COL1) FROM TBL2)+(SELECT COUNT(COL1) FROM TBL3)
-
(SELECT SUM(TOTAL) FROM FILE1 WHERE FeeType = 'ABC'))  AS VARIANCE

How can I use the column aliases in this part?


((SELECT COUNT(COL1) FROM TBL1)+(SELECT COUNT(COL1) FROM TBL2)+(SELECT COUNT(COL1) FROM TBL3) - (SELECT SUM(TOTAL) FROM FILE1 WHERE FeeType = 'ABC'))  AS VARIANCE

Something like this


(COMPAREVALUE - BASEVALUE) AS VARIANCE

As for as I see when I give an alias for a column name its mostly used during a select statement so that the alias is displayed as the column header.

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 09:30:23

[code]Select Columns ,(COMPAREVALUE - BASEVALUE) AS VARIANCE from
(
your_Query
) T[/code]

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-12 : 09:57:24

quote:
Originally posted by madhivanan

Select Columns ,(COMPAREVALUE - BASEVALUE) AS VARIANCE from
(
your_Query
) T

Madhivanan

Failing to plan is Planning to fail

I'm getting invalid column name error for CompareValue and Basevalue...

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 09:58:44

Post the full query you used

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-12 : 10:15:52

[code]
SELECT '007' AS DRIVER,BASEVALUE,COMPAREVALUE,(COMPAREVALUE - BASEVALUE) AS VARIANCE FROM
((SELECT SUM(TOTAL) FROM FILE1 WHERE FeeType = 'ABC') AS BASEVALUE,
((SELECT COUNT(COL1) FROM TBL1)+(SELECT COUNT(COL1) FROM TBL2)+(SELECT COUNT(COL1) FROM TBL3))
AS COMPAREVALUE) T
[/code]

Thanks

Karunakaran

TimS
Posting Yak Master

198 Posts

Posted - 2005-10-12 : 18:24:16

SELECT '007' AS DRIVER, BASEVALUE, COMPAREVALUE, (COMPAREVALUE - BASEVALUE) AS VARIANCE FROM
(
SELECT
(SELECT SUM(TOTAL) FROM FILE1 WHERE FeeType = 'ABC') AS BASEVALUE,
((SELECT COUNT(COL1) FROM TBL1) + (SELECT COUNT(COL1) FROM TBL2) + (SELECT COUNT(COL1) FROM TBL3))
AS COMPAREVALUE
) AS T

This syntax checks OK for me.
Tim S

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-13 : 00:15:17

Thanks Tim.

Let me check it out.

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources