Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List number of rows per field value in SQL

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.

like image 342
Brennan Vincent Avatar asked Jun 21 '10 17:06

Brennan Vincent


People also ask

How do I count the number of rows with a specific value in SQL?

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).

How do I count a field value in SQL?

SQL COUNT Function If we define a column in the COUNT statement: COUNT ([column_name]), we count the number of rows with non-NULL values in that column. We can specify to count only unique values by adding the DISTINCT keyword to the statement.

What is @@ rowcount?

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

How do I find the number of rows in a row in SQL?

If you'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function.


2 Answers

Simple SQL

select EmployeeId,count(*)
from YourTable
Group by EmployeeId
like image 132
josephj1989 Avatar answered Sep 23 '22 14:09

josephj1989


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.

like image 31
OMG Ponies Avatar answered Sep 23 '22 14:09

OMG Ponies