Why "SELECT COUNT(DISTINCT <Column>) FROM <Table>" return 0?

Question

I have run the query above for all columns in a massive table (Billion rows) and everything is fine except a couple which are returning 0. How is this possible?

Answer 1

COUNT(DISTINCT) can return zero under two circumstances. The first is that all the values for the column/expression evaluate to NULL. The second is that the WHERE clause (or JOIN) filters out all rows.

If you have no WHERE or JOIN, then the values are all NULL for <Columnb>.