how to count number of rows in a table column

Question

Post reply

how to count number of rows in a table column

ALIF-662928

SSCertifiable

Points: 6054
More actions
April 30, 2008 at 9:30 am

#185905

Hi all,
can any one send me the query to find the number of rows for a particular column,
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Nicole (4/30/2008)
Hi all,
can any one send me the query to find the number of rows for a particular column,
thanks

You have a terminology problem here, so it's hard to understand the question.

Rows aren't parts of table columns; rows are parts of tables.

Perhaps you mean one of these?

select count(Column) from Table

select count(distinct Column) from Table

Post some example data and the result you'd want for that example.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 2

Thanks,

I guess you are right, I thought of somthing else. I have a table and my manager wants to update that column, so he asked me to tell how many rows will be affected in that column of a table when we are updating it. He want me to say the number of rows for that column, and you are right rows count for a table is same for the column.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You can get that specific count for your manager one of two ways. Safely:

SELECT COUNT(*)

FROM MyTable

WHERE MyColumn = 'OldValue'

Or not so safe:

BEGIN TRAN

UPDATE MyTable

SET MyColumn = 'NewValue'

WHERE MyColumn = 'OldValue'

ROLLBACK TRAN

The first query will return the count. The second query will return the number of rows affected, but it won't do anything. However, you are mucking with the data, just rolling it back, so you will put a load on the system. It's not as safe.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 4

Thanks Grant Fritchey,

It helped me a lot. I have a problem in creating a view for my production db, i cannot post it here, if you can help me pls give me your email id, Its really critical for me to safe my job.

Thanks again.