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.
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).
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.
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.
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.
Simple SQL
select EmployeeId,count(*)
from YourTable
Group by EmployeeId
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With