I am trying to get comfortable creating joins with T-sql 2008

Question

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 the 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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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