count(*) vs count(col): The Difference Is NULL

count() works in both ClickHouse and Spark — it’s standard SQL for counting rows. What actually trips people up every time is whether it counts NULLs.

count(expr) or count(DISTINCT expr)

• COUNT(*) or COUNT(1)
  • * → all columns (a whitelist)
  • includes NULL
• COUNT(col)
  • only counts rows where col is not NULL

The difference between the two is exactly the number of rows missing that value — which is also the most direct way to answer “how many sign-ups didn’t fill in an email.”

Using CASE WHEN to count several conditions at once

SELECT
    COUNT(CASE WHEN salary > 100000 THEN 1 ELSE NULL END) AS high_earners,
    COUNT(CASE WHEN salary <= 100000 THEN 1 ELSE NULL END) AS standard_earners
FROM employees;

• The trick: COUNT(col) only increments on non-NULL, so write the branch you don’t want counted as NULL.

---

References

• ClickHouse count — https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/count
• Spark SQL Built-in Functions — https://spark.apache.org/docs/latest/api/sql/

Related: back to the Cross-Engine DB Query overview, or see it inside the portable SQL core.