Using Alias in SQl - 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)

Using Alias in SQl

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-17 : 07:35:24

Balaji writes "How to sum an expression which is defined as Alias Name?

For Example:
Select Sum(col1) as 'Total1', Sum(Col2) as 'Total2',
from Temp1 group by Col1, Col2

I want the Sum of 'Total1' and 'Total2' as new Expression

Can any one help me?

Regards

Balaji"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 07:44:10

HTH..
Select Sum(col1) as 'Total1', Sum(Col2) as 'Total2',
from Temp1

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-17 : 08:37:39

Guess you want something more general than just adding your sums together.

select Total1 + Total2
from
(
Select Sum(col1) as Total1, Sum(Col2) as Total2,
from Temp1 group by Col1, Col2
) as a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dsdeming

479 Posts

Posted - 2003-04-17 : 08:39:02

If I understand correctly, you're looking for something like this:

SELECT SUM(col1) AS Total1, SUM(Col2) AS Total2, SUM(Col1) + SUM(Col2) AS Total3
FROM Temp1
GROUP BY Col1, Col2

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 09:40:22

Something makes me think the real answer is a little bit deeper...because:

quote:

Select Sum(col1) as 'Total1', Sum(Col2) as 'Total2',
from Temp1 group by Col1, Col2

doesn't make any sense, does it?

I mean, would this even work?

ok, ok, I just tested it, and it does work (why am I not suprised with SQL Server). But it doesn't have any refernece, and just spits out numbers, which are just meaningless....

Can some one help me here, am I totally missing the boat?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-17 : 22:04:54

quote:

why am I not suprised with SQL Server

that statement is perfectly legal in all fully ANSI compliant forms of SQL. There's nothing inherently wrong with SQL Server for accepting that statement w/o giving an error.

sure, it makes no sense and you would not want to do it, but there is nothing wrong with it. like saying:

SELECT 2343 + 0

Why would you do that? seems silly but it should certainly be allowed.

- Jeff

Edited by - jsmith8858 on 04/17/2003 22:05:59

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-18 : 12:00:09

Jeff,

Gotta disagree:

quote:

SELECT 2343 + 0

Is a valid statement. There is no such thing as a date of space or a number as space. There is such a thing as the nonexistance of thos datatypes, but not space.

Plus the brilliant wonder boys over at MS decided that they would let SQL Server "interprete" those values to mean something...

Which is:

quote:

Select Convert(datetime, ' ' ), Convert (int, ' ')
------------------------------------------------------ -----------
1900-01-01 00:00:00.000 0

(1 row(s) affected)

WHY

This should throw an error... just like:

Select Convert(datetime, 'A' )
Select Convert (int, 'A')

What's the difference?

I imagine it was done for 1 of 2 reasons.

1: Early SQL Server Developers couldn't grasp the concept, or
2: It was easier for the SQL Server development team (as awy to deal with nulls)

ANYWAY, AS USUAL, JUST MOO.

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-19 : 10:52:15

Brett -- why are you talking about converting datetimes???

I am specifically referencing:

quote:

Select Sum(col1) as 'Total1', Sum(Col2) as 'Total2',
from Temp1 group by Col1, Col2

You indicated you felt SQL Server should not accept the above statement, and hinted it was a fault of SQL for accepting it.

And as I said, that is a valid SQL statemnt, and it would be a bug in SQL Server if it DIDN't accept it. I did give an example of another valid satement that maybe makes no sense to want to do, I hope that didn't cause confusion. I am not speaking about SQL Server's overall adherence to ANSI specs, just the fact that SQL Server should not be faulted or singled out for accepting the above statement -- it's perfectly valid SQL.

Whether or not it logically makes sense to code a SQL statement that way is another thing all together, as are the methods SQL uses to convert character strings to ints or datetimes.

- Jeff

Edited by - jsmith8858 on 04/19/2003 10:53:27

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-04-19 : 12:13:58

The statement


Select Sum(col1) as 'Total1', Sum(Col2) as 'Total2'
from Temp1 group by Col1, Col2

is not ANSI sql compliant. What comes after as should be an identifier, not a string.

so


Select Sum(col1) as Total1, Sum(Col2) as Total2
from Temp1 group by Col1, Col2


Select Sum(col1) as "Total1", Sum(Col2) as "Total2"
from Temp1 group by Col1, Col2

is legit ANSI SQL.

That SQL server does not reject invalid datetime values is also non-standard behaviour. I wonder how that question entered the debate though.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 09:54:51

Ladies and Gentlemen,

My point to make was that there are no reference to what the numbers would mean. Take the following example"


CREATE TABLE Table1 (Col1 varchar(5), col2 int, col3 datetime, userId varchar(8))
GO

DECLARE @x Int
SELECT @x = 0
WHILE @x < 1000 BEGIN
INSERT INTO Table1 (Col1, col2, col3, UserId)
SELECT 'AAA', 1, Convert(datetime,'2001-01-01'), 'x002548' UNION ALL
SELECT 'AAA', 1, Convert(datetime,'2002-01-01'), 'x002549' UNION ALL 
SELECT 'AAA', 2, Convert(datetime,'2003-01-01'), 'x002548' UNION ALL
SELECT 'AAA', 2, Convert(datetime,'2004-01-01'), 'x002549' UNION ALL 
SELECT 'AAB', 1, Convert(datetime,'2005-01-01'), 'x002548' UNION ALL
SELECT 'AAB', 3, Convert(datetime,'2006-01-01'), 'x002549' UNION ALL 
SELECT 'AAC', 2, Convert(datetime,'2007-01-01'), 'x002548' UNION ALL
SELECT 'AAD', 2, Convert(datetime,'2008-01-01'), 'x002549' 
SELECT @x = @x +1
END
GO


CREATE INDEX IX1 ON Table1 (col3)
GO

UPDATE STATISTICS Table1
GO

SELECT SUM(Col2) FROM Table1 GROUP BY Col2
GO

DROP TABLE TABLE1
GO

What does that do for you? I suggested that the developer was trying to do something more, and that we didn't have a clear picture.

Also my mention of convert space to datetime and numeric is just a pet peeve of mine...because I've seen people do it. Why? I have no idea. I'm just saying, it blew my mind the first time I saw, because I didn't think it was possible. I understand that syntactically the group by (whil not ANSI compliant) does work (and is becoming more pervasive across all rdbms's now), but you still lose the relavence as to what the numbers mean.

I mean unless I'm total off course. Any other insight appreciated.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources