problem with group by

hi im new in sql and i wonder if somone can help me here

im trying to make a select statmant on the northwind database and im trying to do group by at the end

this is my query

select * from employees

select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
group by 'senior'

whay cant i group somthing that i have aliased is there another whay

thk

Hi,

When you perform a group by, each column from the select clause must be:

included in the group by clause|||

Hi,

you will either have to repeat the whole expresion:

select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
group by e.lastname + ' ' + e.firstname,
e2.lastname + ' ' + e2.firstname

or use a subquery:

SELECT senior, officer
FROM
(
select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
) Subquery
group by senior,Officer
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Indeed you can use a subquery as Jens mentioned.
Just make sure that all the columns are or defined with an aggregate function or added in the group by clause.

So, for the second sample of Jens, you need to add officer to the group by clause or define it with an aggregate function:
SELECT senior, MAX(officer)
FROM
(
select e.lastname + ' ' + e.firstname as senior
, e2.lastname + ' ' + e2.firstname as officer
from employees as e inner join employees as e2
on e.reportsto = e2.employeeid
) Subquery
group by senior,Officer

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

When Road is straight forward why need to get down by Road. It will be a faster because it is not using SubQuery.

SELECT
Senior = E.LastName + ' ' + E.FirstName,
Officer = E2.LastName + ' ' + E2.FirstName
FROM
Employees E
INNER JOIN Employees E2
ON E.ReportsTo = E2.EmployeeID
GROUP BY
(E.LastName + ' ' + E.FirstName),
(E2.LastName + ' ' + E2.FirstName)

|||

Because this was a way to show him how to use the aliases anyway.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Column aliases in the outer most query can only be used in the ORDER BY clause and it is returned to the client as part of the metadata. It cannot be used in other clauses (WHERE, GROUP BY or HAVING). This is according to ANSI SQL specifications. So you can either repeat the