Wrong count result

user_16473751 0
Viewed 63

I have 2 tables in sql server: Employee (Job Title, Hire Date)

  • Person (FirstName, LastName) And I need to return the E.JobTitle, E.HireDate,P.FirstName ,P.LastName along with the how many employees have the same job title.

so I used this query:

SELECT E.JobTitle,
       E.HireDate,
       P.FirstName,
       P.LastName,
       COUNT(E.JobTitle)
FROM AdventureWorks2019.HumanResources.Employee E
     JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID
GROUP BY E.JobTitle,
         E.HireDate,
         P.FirstName,
         P.LastName;

the problem is that the query returns 1 per the count, while I'd expect to get per each row the count of the num employees with that job title. my question is how can I get the correct count?

count sql-server
2 Answers

You should count the number of employee for each job title first in a subquery or cte.

SELECT
        JobTitle,
        COUNT(*) AS Count
    FROM HumanResources.Employee
    GROUP BY JobTitle

Then you can join the subquery or the cte with your original query.

WITH cte
AS
(
    SELECT
            JobTitle,
            COUNT(*) AS Count
        FROM HumanResources.Employee
        GROUP BY JobTitle
)
SELECT
    E.JobTitle,
    E.HireDate,
    P.FirstName,
    P.LastName,
    cte.Count
    FROM
        HumanResources.Employee E
        INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID
        INNER JOIN cte on cte.JobTitle = E.JobTitle
    ORDER BY
        E.JobTitle,
        P.LastName,
        P.FirstName
;
user_27944090

As mentioned by @Larnu in the comments, your issue is that you're grouping by LastName also, so you are going to get a count of 1 per LastName.

You need a windowed count with OVER, not an aggregated one with GROUP BY

No extra joins or grouping needed.

SELECT E.JobTitle,
       E.HireDate,
       P.FirstName,
       P.LastName,
       COUNT(*) OVER (PARTITION BY E.JobTitle)
FROM AdventureWorks2019.HumanResources.Employee E
     JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID;
user_148689970
Related