Home > Archive > SQL Server Programming > September 2005 > Error in GroupBy
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Wayne Wengert 2005-09-30, 8:02 am |
| 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
| |
|
| SQL 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
>
| |
| Tibor Karaszi 2005-09-30, 8:02 am |
| 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 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
>
| |
| David Portas 2005-09-30, 8:02 am |
| ....
GROUP BY LastName, FirstName, Caption
--
David Portas
SQL Server MVP
--
|
|
|
|
|