SQL Count aggregate question, combining 3 tables

Question

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.

The problem here is you're grouping on every column, so every rowset is unique (and so no results are returned). Without really seeing the data the best we can do here is guess. Seems like you should just be returning values from the Employees table though, and grouping on those. — Thom A
– Thom A ♦, Commented May 13, 2019 at 18:41
@CouponCode I see you use StartDate and End_Date I think it didn't happen on purpose but try to keep it the same :) — JochemQuery
– JochemQuery, Commented May 13, 2019 at 18:54

Gordon Linoff · Accepted Answer · 2019-05-13 18:41:20Z

1

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.

answered May 13, 2019 at 18:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CouponCode Over a year ago

This is my beginner's database class, I thank you very much. Though I spent hours troubleshooting what you just helped me with in a few minutes, I feel like I learned! I have never heard of window functions, but I am going to read all about them now. I knew I shouldn't use the project columns in the group by, I just didn't know how not to. Thanks again.

Collectives™ on Stack Overflow

SQL Count aggregate question, combining 3 tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related