CODEWITHSUNDEEP
sql-servercount
Lihi Eldor
Lihi Eldor

Reputation: 35

Wrong count result

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

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?

Upvotes: 2

Views: 75

Answers (2)

Charlieface
Charlieface

Reputation: 71579

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;

Upvotes: 1

Han
Han

Reputation: 3072

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
;

Upvotes: 1

Related Questions