CODEWITHSUNDEEP
sqlsql-server
Tyler
Tyler

Reputation: 3813

Multiple grouped items

I can't seem to find out how to get the functionality I want. Here is an example of what my table looks like:

EmpID     |  ProjectID     |    hours_worked   | 
  3             1                    8
  3             1                    8
  4             2                    8
  4             2                    8
  4             3                    8
  5             4                    8

I want to group by EmpID and ProjectID and then sum up the hours worked. I then want to inner join the Employee and Project table rows that are associated with EmpID and ProjectID, however when I do this then I get an error about the aggregate function thing, which I understand from research but I don't think this would have that problem since there will be one row per EmpID and ProjectID.

Real SQL:

SELECT
    WorkHours.EmpID,
    WorkHours.ProjectID,
    Employees.FirstName
FROM WorkHours
INNER JOIN Projects ON WorkHours.ProjectID = Projects.ProjectID
INNER JOIN Employees ON WorkHours.EmpID = Employees.EmpID
GROUP BY WorkHours.ProjectID, WorkHours.EmpID

This gives the error:

Column 'Employees.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 1

Views: 57

Answers (4)

Sam CD
Sam CD

Reputation: 2097

You might want to use OVER (PARTITION BY) so you won't have to use GROUP BY:

Select a.EmpID
,W.ProjectID
,W.SUM(hours_worked) OVER (PARTITION BY W.EmpID,W.ProjectID)
,E.FirstName
FROM WorkHours W
INNER JOIN Projects P ON WorkHours.ProjectID = Projects.ProjectID
INNER JOIN Employees E ON WorkHours.EmpID = Employees.EmpID

Upvotes: 3

Jamiec
Jamiec

Reputation: 136134

One way is to use a CTE to first form the data you want, then join onto the other table(s)

WITH AggregatedHoursWorked
AS
(
SELECT EmpID,
       ProjectID,
       SUM(HoursWorked) AS TotalHours
FROM WorkHours
GROUP BY EmpID, ProjectID
)

SELECT e.FirstName
       p.ProjectName,
       hw.TotalHours
FROM AggregatedHoursWorked hw
INNER JOIN Employees e
   ON hw.EmpID = e.ID
INNER JOIN Projects p
   ON hw.ProjectID = p.ID

Upvotes: 1

DavidG
DavidG

Reputation: 119017

You can do a basic query to get the grouped hours and use that as a basis for the rest, either in a CTE or as a subquery. For example, as a subquery:

SELECT *
FROM
    (SELECT EmpID, ProjectID, SUM(hours_worked) as HoursWorked 
    FROM WorkHours
    GROUP BY EmpID, ProjectID) AS ProjectHours
JOIN Projects
    ON Projects.ID = ProjectHours.ProjectID
JOIN Employees
    ON Employees.ID = ProjectHours.EmpID

Upvotes: 2

Sergio Internicola
Sergio Internicola

Reputation: 347

If you use an aggregate function, all the columns must be named in the aggregate function and/or in the GROUP BY clause. If you want to join the descriptions (normally unique for a given ID), you have to include the description columns in the GROUP BY clause. This will not affect the result of the query.

Upvotes: 0

Related Questions