Count Number of Rows per Employee

Question

Count Number of Rows per Employee

clynch 35347

SSC Rookie

Points: 32
More actions
October 17, 2011 at 1:51 pm

#246663

This is what is in my table
EmpidName EmergContact
123Smith, JimSmith, Judy
123Smith, JimSmith, John
596Doe, JaneDoe, John
596Doe, JaneDoe, Chris
There is no number field for EmergContact. How do I get it as:
EmpidName EmergContactNumber
123Smith, JimSmith, Judy1
123Smith, JimSmith, John2
596Doe, JaneDoe, John1
596Doe, JaneDoe, Chris2
I've tried Count(), Count(Distinct Empid), @@RowCount but nothing works.
Any help would be much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Have a look at the Row_Number() windowed function.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

Looks a lot like homework, so I'll offere a hint to help you get along.

~~you need to use GROUP BY employee along with a count(); i think that will get you were you want to be;~~

that will get you the count, sorry; the row_number() suggestion will get you the arbitrary counter per employee, as suggested.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

SELECT Empid, Name, EmergContact,

ROW_NUMBER() OVER (PARTITION BY Empid order BY Name) [Number]

FROM table

Converting oxygen into carbon dioxide, since 1955.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Steve Cullen (10/17/2011)
SELECT Empid, Name, EmergContact, ROW_NUMBER() OVER (PARTITION BY Empid order BY Name) [Number]
FROM table

Yup - that should pretty much do it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

clynch 35347 SSC Rookie Points: 32 More actions · Answer 5

That did it. It's always the trivial things that stump me...

Thanks guys.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events