Error in GroupBy

I have the query shown below which throws an error that "JudgeName" is an
invalid column name? Is using an alias not allowed here?
Wayne
================= code ==============
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By JudgeName, CaptionSQL BASIC RULE: YOU CAN'T USE ALIAS IN GROUP BY
Method 1:
SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ',' + Names.FirstName), Caption
Method 2
SELECT a.JudgeName from
(SELECT (Names.LastName + ',' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Caption ) a GROUP BY a.JudgeName
Regards
R.D
"Wayne Wengert" wrote:

> I have the query shown below which throws an error that "JudgeName" is an
> invalid column name? Is using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>
>|||Wayne,
Unfortunately not. You can use the expression that makes up JudgeName. For
example,
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By (Names.LastName + ', ' + Names.FirstName), Caption
- or -
SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
FROM JudgeEvals
Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
INNER JOIN Names ON Names.NameID=Judges.NameID
Group By Names.LastName, Names.FirstName, Caption
Hope this helps,
Yosh
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ugo4kVbxFHA.3644@.TK2MSFTNGP11.phx.gbl...
>I have the query shown below which throws an error that "JudgeName" is an
>invalid column name? Is using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>|||Locally, the GROUP BY is performed before the SELECT list, hence you cannot
group by a column alias.
Either repeat the expression in GROUP BY or use a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ugo4kVbxFHA.3644@.TK2MSFTNGP11.phx.gbl...
>I have the query shown below which throws an error that "JudgeName" is an i
nvalid column name? Is
>using an alias not allowed here?
> Wayne
> ================= code ==============
> SELECT (Names.LastName + ', ' + Names.FirstName) As JudgeName
> FROM JudgeEvals
> Inner Join Judges On Judges.JudgeID = JudgeEvals.JudgeID
> INNER JOIN Names ON Names.NameID=Judges.NameID
> Group By JudgeName, Caption
>|||...
GROUP BY LastName, FirstName, Caption
David Portas
SQL Server MVP
--