List number of rows per field value in SQL

Question

Hi, database/SQL novice here.

I have a table with a column called, for example, "EmployeeID". I want a query that will, for each distinct employeeID, return the number of rows that have that as their ID. I hope it's clear what I'm trying to do and someone will know how to help!

I don't think it should matter, but just in case, I'm using MS SQL Server 2008.

Answer 1

This should do the trick:

SELECT employeeID, COUNT(employeeID) FROM Employees GROUP BY employeeID

Answer 2

Simple SQL

select EmployeeId,count(*)
from YourTable
Group by EmployeeId

Answer 3

Use:

  SELECT t.employeeid,
         COUNT(*) AS num_instances
    FROM TABLE t
GROUP BY t.employeeid

COUNT is an aggregate function, which requires the use of a GROUP BY clause.

Answer 4

select count(*) AS RowCount, EmployeeID
FROM table
GROUP BY EmployeeID

Answer 5

SELECT DISTINCT employeeID,
COUNT(employeeID) AS [Count]
FROM Employees
GROUP BY employeeID