Not getting the correct count in SQL

Question

Hi,

I am totally new to SQL. I have a simple select query similar to this:

SELECT COUNT(col1) FROM table1

There are some 120 records in the table and shown on the GUI. For some reason, this query always returns a number less than the actual count.

Can somebody please help me?

Answer 1

Try

select count(*) from table1

Edit: To explain further, count(*) gives you the rowcount for a table, including duplicates and nulls. count(isnull(col1,0)) will do the same thing, but slightly slower, since isnull must be evaluated for each row.

Answer 2

You might have some null values in col1 column. Aggregate functions ignore nulls. try this

SELECT COUNT(ISNULL(col1,0)) FROM   table1

Answer 3

Slightly tangential, but there's also the useful

SELECT count(distinct cola) from table1

which as you may surmise gives you number of distinct cola in the table