SQL record count

[ebyong77]

hi i need to know how to query a number of count on a particular table
let say i have emp_id and emp_name field. on my table their are 2 records.

the result looks this:

rowcount | emp_id | emp_name
1 1000 albert
2 2000 leah

is it possible?

thanks,
albert

[Geek-Master]

If your table has multiple records per employee as in

EmployeeID, EmployeeName
------------------------------------
12345, John Smith
12346, Jane Doe
12345, John Smith
12347, Bob Smith

You can group these records together, and use the aggregate function "COUNT" to get a number of rows each employee has in the table. This can be done using this script

SELECT EmployeeID, EmployeeName, COUNT(*) as RowCount FROM TableA
GROUP BY EmployeeID, EmployeeName
ORDER BY RowCount DESC --this will sort most row count first

Since Employee, John Smith, has two records in the table, his row count will be 2, while Jane Doe and Bob Smith will only have 1.

EmployeeID, EmployeeName, RowCount
-------------------------------------
12345, John Smith, 2
12346, Jane Doe, 1
12347, Bob Smith, 1

Hope this helps

[ravinder007]

Hi ebyong77

In sql server 2005 you can use this query to get rownumber

SELECT     emp_name, ROW_NUMBER() OVER (ORDER BY emp_id) AS 'ROWCOUNT'
FROM        yourtable name

Row_number retrurn the serial number of records.