Hi there is a table

id ib it NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 1 2 2 3 2 2 4 2 3 NULL 5 2 NULL 5 2 4 6 2 1 6

Request

  SELECT COUNT(*), COUNT(ib), COUNT(DISTINCT ib) FROM tbl

returns 10 7 3

The number 10 is the number of records in the table.

The number 3 is the number of unique entries in the column, discarding NULL

And where does 7 come from?

How to get the number of records in the table except for the sample where all 3 values ​​are NULL ?

  • 2
    This is the number of records that are not NULL. It is known that a comparison of any value with NULL results in a false renegator
  • one
    maybe you somewhere sealed? Such results should not return your query, but the following: `SELECT COUNT (*), COUNT (id), COUNT (DISTINCT ib) FROM your_table` - DreamChild
  • Yes, really an error in the data .. Thank you. Your comments helped ... Ie, I correctly understood that COUNT (id) returns the number of values ​​not null, and COUNT (DISTINCT id) returns the number of non-repeatable, non-null values? - zloctb

1 answer 1

And where does 7 come from?

For submitted data, there should be 5 — the number of non-NULL values ​​in the ib column.

How to get the number of records in the table except for the sample where all 3 values ​​are NULL?

 select count(*) from table where id is null and ib is null and it is null
  • Grateful to everyone. - zloctb