CODEWITHSUNDEEP
sqlsql-servercount
CouponCode
CouponCode

Reputation: 1

SQL Count aggregate question, combining 3 tables

I am trying to join 3 tables to include only those employees who are assigned to more than one assignment. Through troubleshooting, I have found that my query works until I add my third table. Specifically, until I add the third table's columns to group by. I need the third table added, as it included the start and end date of the projects.

I have been at this for days. I have tried string_agg to bypass using the columns from the third table in the group by, but found that it it not supported in the program we're required to use. I have tried different joins, different counts, using variables. I have tried joining the tables in different combinations and subsequently worked through other errors. I have tried to do nested select statements.

At this point, I have lost count. I started out with a cartesian product and have now worked out that I think the problem lies somewhere with grouping third(projects) table.

CREATE VIEW Assignments AS

    SELECT a.EmpIDNumber,
           COUNT(*) AS Frequency,
           e.FirstName,
           e.LastName,
           a.PositionOnProject,
           P.AssignedToProject AS ProjectName,
           P.ProjectBeginDate AS StartDate,
           P.FinishOnProject AS End_Date
    FROM Assignments a
         INNER JOIN Employees e ON a.EmpIDNumber = e.EmpIDNumber
         INNER JOIN Projects p ON a.projectID = p.ProjectID
    GROUP BY e.FirstName,
             E.LastName,
             a.PositionOnProject,
             a.EmpIDNumber,
             p.AssignedToProject,
             p.ProjectBeginDate,
             p.FinishOnProject
    HAVING COUNT(*) > 1;

As is, I get no results. Just empty headers. Removing the third table(projects) from the select and group by, I get the expected results, showing those employees who are assigned to more than one project.

I know I am doing something wrong and am open and eager to learn what that is. Any help in doing that is appreciated.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If you want project details for employees assigned to more than one project, then use window functions:

SELECT a.EmpIDNumber, a.Frequency,
       e.FirstName, e.LastName, a.PositionOnProject,
       P.AssignedToProject AS ProjectName,
       P.ProjectBeginDate AS StartDate,
       P.FinishOnProject AS End_Date
FROM (SELECT a.*, COUNT(*) OVER (PARTITION BY a.EmpIDNumber) as frequency
      FROM Assignments a
     ) a INNER JOIN
     Employees e
     ON a.EmpIDNumber = e.EmpIDNumber INNER JOIN
     Projects p
     ON a.projectID = p.ProjectID
WHERE frequency > 1;

Your problem is that you have added the project information to the GROUP BY. Only one project is going to match those conditions, so the HAVNG clause ends up filtering everything out.

Upvotes: 1

Related Questions