Search code examples
sqldatabasecountdistinctinformix

Why does SELECT DISTINCT return a different number of rows than COUNT(DISTINCT)?

I just noticed something strange on some of my Informix SQL columns (in the same table). When I do this query

SELECT DISTINCT colName
FROM myTable

I get, for example, 40 rows. But when I do

SELECT COUNT(DISTINCT colName)
FROM myTable

I get a significantly bigger number (like, for example, 300). On some columns it works, however (number of rows and count result are equal). If I do this:

SELECT colName
FROM myTable

I get the same number of rows that

SELECT COUNT(*)
FROM myTable

gives, which is yet another (much higher) number, nameley the exact number of all rows in the table.

Why does the number returned from COUNT (DISTINCT) and the row-number returned by the regular SELECT DISTINCT differ on some columns but not on others?

Solution

  • According to the MSDN documentation on COUNT:

    DISTINCT Specifies that COUNT returns the number of unique nonnull values.

    SELECT DISTINCT, on the other hand, will include null values in the result set.

    Not sure if that gets you all the way to a solution, but I suspect the null values have something to do with it.