Invalid Column Name -- sql server programming

Norman Yuan
10/1/2007 5:21:40 PM

Because grouping happens before the "division" alia is assigned to the
column as the dataset is finally generated.

So, you have to:

Select...
....
Group By divisioncode_i,jobtitle,department

[quoted text, click to view]

Alex Kuznetsov
10/1/2007 5:24:46 PM

[quoted text, click to view]

use this:

select count(employid), left(divisioncode_i,1) as division, jobtitle,
left(deprtmnt,2) as department
from upr00100
group by left(divisioncode_i,1), jobtitle, left(deprtmnt,2)

AlterEgo
10/1/2007 5:26:43 PM

Cindy,

SQL Server doesn't permit aliases in the order by or group by clauses. Try
changing your group by clause to:
group by left(divisioncode_i,1) , jobtitle, left(deprtmnt,2)

-- Bill

[quoted text, click to view]

Cindy Mikeworth
10/1/2007 7:53:05 PM

What's wrong with this statement? I'm getting an invalid column name on
Division and Department. I'm trying to get a count of all unique
combinations of the three columns.

select count(employid), left(divisioncode_i,1) as division, jobtitle,
left(deprtmnt,2) as department
from upr00100
group by division, jobtitle, department

Steve Dassin
10/1/2007 7:56:16 PM

[quoted text, click to view]

From a logical point of view there 'nothing' wrong. This makes perfect
sense and there are systems that follow this exact logic in grouping.

[quoted text, click to view]

Lets be clear about the 'Thanks'. Here someone is thanking people
for taking them a step 'back' logically!

Perhaps William Shakespear said it best:
"Where ignorance is bliss, 'tis folly to be wise."

Welcome to the world of sql :-)

www.beyondsql.blogspot.com

Andrew J. Kelly
10/1/2007 8:19:55 PM

Cindy,

Well you spelled Department two different ways so I don't know if that is
the correct spelling or not without seeing the DDL. But you need to have the
same expression for the group by as the ones in the select list.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

[quoted text, click to view]

Cindy Mikeworth
10/1/2007 10:26:29 PM

Tibor Karaszi
10/2/2007 12:00:00 AM

AlterEgo,

[quoted text, click to view]

You *can* use an alias in ORDER BY. In fact, if SQL were a bit stricter, then alias would be what
you could use (and not the original column name or expression). ORDER BY if performed after the
SELECT list (which is performed after GROUP BY) so at this point (the ORDER BY point) we have the
column aliases.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Invalid Column Name