SELECT using functions with names

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)

SELECT using functions with names

Author

Topic

lokelo
Starting Member

2 Posts

Posted - 2007-12-10 : 20:09:50

Why is it that this query


select tm_date, sum(tm_hours) hours, (8 / sum(tm_hours) ) num_employees 
FROM easy_timesheet_hours
group by tm_date

will return a result set but this one, using the "hours" column name does not?

select tm_date, sum(tm_hours) hours, (8 / hours) num_employees 
FROM easy_timesheet_hours
group by tm_date

I'm sure I've done this with MySQL before. Is there some option i need to set to enable this behavior? I'm doing something similar with sub selects instead of the simple sum function and this feature would be great to have.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-10 : 22:26:01

IIRC you can only refer to the column by alias in an ORDER BY or GROUP BY clause. Not in the select list or where clause.

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-11 : 02:04:28

You can only directly use alias name in ORDER BY clause. If you want to refer alias name, make use of derived table

select *, (8 / hours*1.0) num_employees from
(
select tm_date, sum(tm_hours) hours FROM easy_timesheet_hours
group by tm_date
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources