CODEWITHSUNDEEP
sqlsql-server-2008
Burzum619
Burzum619

Reputation: 143

Counting number of occurrences in subquery

My task is to find the number of occurrences of late timesheet submissions for each employee in our database. There are two tables which I have primarily been looking at, but I'm having trouble putting the two together and coming up with a decent view of the COUNT of occurrences and the employee ID for which they are associated with.

I have created this query which provides me with the EmployeeID for each occurrence.

SELECT db.Employee.EmployeeID 
FROM db.LateTimesheets
INNER JOIN db.Employee ON Employee.LastName = LateTimesheets.LastName AND Employee.FirstName = Late Timesheets.FirstName

Now, with this simple query I have a view of the EmployeeID repeated however many times these incidents have occured. However, what I ultimately want to end up with is a table that displays a count for each occurance, along with the EmployeeID for which this count is associated with.

I would assume I would need to use the COUNT() function to count the amount of rows for each EmployeeID, and then select that value along with EmployeeID. However, I am having trouble structuring the subquery correctly, and everything I have tried thus far has only generated errors with MS SQL Server Management Studio.

Upvotes: 3

Views: 4076

Answers (3)

Thomas
Thomas

Reputation: 64635

A simpler version of usr's answer would be the following which avoids the construction of the derived table:

Select db.Employee.EmployeeID, Count( db.LateTimesheets.somecolumn ) As Total
From db.Employee 
    Left Join db.LateTimesheets
        On LateTimesheets.LastName  = Employee.LastName
            And Late Timesheets.FirstName = Employee.FirstName
Group By db.Employee.EmployeeID

Upvotes: 2

Anthony Grist
Anthony Grist

Reputation: 38345

I may have misunderstood the question, but wouldn't GROUP BY solve your problem?

SELECT COUNT(db.LateTimesheets.somecolumn), db.Employee.EmployeeID 
FROM db.LateTimesheets
INNER JOIN db.Employee ON Employee.LastName = LateTimesheets.LastName 
    AND Employee.FirstName = Late Timesheets.FirstName
GROUP BY db.Employee.EmployeeID

Just replace somecolumn with the name of a column that's actually in the table.

Upvotes: 1

usr
usr

Reputation: 171188

select e.*, isnull(lt.Count, 0) as Count
from Employee e
left join (
 select LastName, count(*) as Count from LateTimesheets
) LateTimesheets lt on e.LastName = lt.LastName

The trick is to do the grouping in a derived table. You don't want to group everything, just the LateTimesheets.

We need a left join to still get employees with no LateTimesheets.

Upvotes: 0

Related Questions