Not getting the correct count in SQL

问题:

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 which is less than the actual count.

Can somebody please help me?

回答1:

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

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

回答2:

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.

回答3:

Slightly tangential, but there's also the useful

SELECT count(distinct cola) from table1

which gives you number of distinct column in the table.

回答4:

You are getting the correct count

As per https://docs.microsoft.com

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates an expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates an expression for each row in a group and returns the number of unique, non null values.

In your case you have passed the column name in COUNT that's why you will get count of not null records, now you're in your table data you may have null values in given column(col1)

Hope this helps!