In T-SQL, aggregate functions(ex. COUNT(), SUM()) return NULL value or raise Error if all values are NULL or no rows. Here’s query count rows correctly even if all column values are NULL or no rows hit.
-- Count rows(exclude NULL value) -- even if add "WHERE foo > 1", return value is also"0"(not NULL) SELECT ISNULL(SUM(CASE WHEN bar IS NULL THEN 0 ELSE 1 END), 0) FROM ( SELECT 1 foo, NULL bar ) X >> 0 -- Count rows(include NULL value) -- if add "WHERE foo > 1", return value turns to "0" SELECT COUNT(ISNULL(bar, 1)) FROM ( SELECT 1 foo, NULL bar ) X >> 1
* please change “ISNULL” to “IFNULL” in MySQL or SQLite.