I am trying to get comfortable creating joins with T-sql 2008
I am trying to write this select statement.
My select statement consists of a join that returns all of the first and last names of my fictional employees, their department names and I am trying to group the individuals by their respective departments. This is th开发者_如何学Goe code I wrote:
select e.First_Name,e.Last_Name,Department_Name
from EMPLOYEES e
join DEPARTMENTS
on e.Department_Id = DEPARTMENTS.Department_Id
group by Department_Name
This is the error message I received:
Msg 8120, Level 16, State 1, Line 1 Column 'EMPLOYEES.First_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What is the best to straighten out this error?
Thank you for your help
When you use a group by on a column/columns SQL summarizes by that column/columns. It is invalid to request/select an employee_name at a department level because there can be many employees in a given department.
If you want to see the Employees in each department you dont need a group by - you can instead use order by as below
select e.First_Name,e.Last_Name,Department_Name
from EMPLOYEES e join DEPARTMENTS on e.Department_Id = DEPARTMENTS.Department_Id order by Department_Name
try this
select e.First_Name,e.Last_Name,d.Department_Name
from EMPLOYEES e
join DEPARTMENTS d
on e.Department_Id = d.Department_Id
group by e.First_Name,e.Last_Name,d.Department_Name
It looks like you might really be looking for an order by
clause.
SELECT
e.First_Name,
e.Last_Name,
d.Department_Name
FROM EMPLOYEES e
INNER JOIN DEPARTMENTS d
ON e.Department_Id = d.Department_Id
ORDER BY Department_Name
The error message gives you all the details you need - the employee First_Name
(and the Last_Name
too for that matter) are not included in an aggregate function or in the group by
clause. If you were to include both First_Name
and Last_Name
in the group by
clause, you're likely not going to see any difference than the original query, unless you have more than one person in a department that has the same first name and last name as someone else in the same department. In this case, the two tuples would be grouped into one result in the resultset
When you use a GROUP BY clause , the SELECT statement could only contain the columns used in the GROUP BY clause or the aggregates.
If you want to Group By 'Department_Name' , each Department is displayed as a Single row in the result and therefore cannot contain multiple employee names in a single row.
If you want the First Name , Last Name , Department Name .. in the Order of their departments ...
You should GROUP BY all the 3 columns with the Department_Name being the first
select e.First_Name,e.Last_Name,d.Department_Name
from EMPLOYEES e
join DEPARTMENTS d
on e.Department_Id = d.Department_Id
group by e.First_Name,e.Last_Name,d.Department_Name
[OR] You can use an ORDER BY statement.
select e.First_Name,e.Last_Name,d.Department_Name
from EMPLOYEES e
join DEPARTMENTS d
on e.Department_Id = d.Department_Id
order by d.Department_Name
精彩评论