MS SQL

MS SQL RSS

SQL record count

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 3

Reputation: ebyong77 is an unknown quantity at this point

Solved Threads: 0

ebyong77

Offline

Newbie Poster

SQL record count

Mar 3rd, 2009

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

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: SQL record count

Mar 5th, 2009

If your table has multiple records per employee as in

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
EmployeeID, EmployeeName
------------------------------------
12345, John Smith
12346, Jane Doe
12345, John Smith
12347, Bob Smith
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT EmployeeID, EmployeeName, COUNT(*) AS RowCount FROM TableA
GROUP BY EmployeeID, EmployeeName
ORDER BY RowCount DESC --this will sort most row count first
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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
EmployeeID, EmployeeName, RowCount
-------------------------------------
12345, John Smith, 2
12346, Jane Doe, 1
12347, Bob Smith, 1
EmployeeID, EmployeeName, RowCount
-------------------------------------
12345, John Smith, 2
12346, Jane Doe, 1
12347, Bob Smith, 1

Hope this helps

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Mar 2009

Posts: 4

Reputation: ravinder007 is an unknown quantity at this point

Solved Threads: 0

ravinder007 ravinder007 is offline

Offline

Newbie Poster

Re: SQL record count

Mar 5th, 2009

Hi ebyong77

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT     emp_name, ROW_NUMBER() OVER (ORDER BY emp_id) AS 'ROWCOUNT'
FROM        yourtable name
SELECT     emp_name, ROW_NUMBER() OVER (ORDER BY emp_id) AS 'ROWCOUNT'
FROM        yourtable name

Row_number retrurn the serial number of records.

Last edited by peter_budo; Mar 6th, 2009 at 8:58 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Ravinder kumar
http://www.ravinderweb.com