PostgreSQL 14: Unexpected NULL Values in Aggregated Result with FILTER Clause
I've tried everything I can think of but I'm working with an scenario with my PostgreSQL 14 query where I'm trying to aggregate data with a `FILTER` clause, and I end up with unexpected NULL values. Here’s the SQL snippet I'm using: ```sql SELECT department, COUNT(*) AS total_employees, COUNT(*) FILTER (WHERE is_active) AS active_employees FROM employees GROUP BY department; ``` The `employees` table has the following structure: ```sql CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), is_active BOOLEAN ); ``` However, when I run this query, I see that for some departments, the `active_employees` count returns NULL instead of 0 when there are no active employees. I expected it to return 0 in these cases. I’ve also tried using `COALESCE` to handle the NULL case, but I want to understand why this is happening in the first place. I’ve verified that there are indeed employees in the departments that are showing NULL, and I’ve run the following query to check the distribution: ```sql SELECT department, is_active, COUNT(*) FROM employees GROUP BY department, is_active; ``` This shows that there are rows with `is_active = false`, but no rows with `is_active = true` for those departments. Is there a reason why the `FILTER` clause behaves this way, and is there a better approach to ensure I get a count of 0 instead of NULL? Any insights or best practices would be greatly appreciated. What am I doing wrong? This is for a service running on macOS.