count(*) vs count(col): It Comes Down to 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. It’s 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.