PostgreSQL query returning unexpected counts when using conditional aggregation
I'm running into an scenario where my PostgreSQL query returns unexpected results when I try to count conditional cases using `FILTER`. I'm using PostgreSQL version 13.3. The goal is to count the number of orders by status for each customer, but I am not seeing the correct number of 'completed' orders when using the following query: ```sql SELECT customer_id, COUNT(order_id) FILTER (WHERE status = 'completed') AS completed_orders, COUNT(order_id) FILTER (WHERE status = 'pending') AS pending_orders FROM orders GROUP BY customer_id; ``` The output shows that the completed orders count is lower than expected, while the pending orders count seems reasonable. For instance, one of my customers has 10 total orders, with at least 7 completed, but the query returns only 5 completed orders. To troubleshoot, I checked the data directly with: ```sql SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id, status; ``` This revealed that there are indeed 7 completed orders for this customer. I suspect that there might be an scenario with how `COUNT` interacts with the `FILTER` clause, but I need to pinpoint it. I've also tried using a `CASE` statement instead: ```sql SELECT customer_id, COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders, COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders FROM orders GROUP BY customer_id; ``` This still returns the same incorrect count for the completed orders. Is there something I'm missing regarding how `COUNT` works with the `FILTER` clause, or could it be related to the underlying data? Any insights would be greatly appreciated, especially if there are best practices for conditional aggregation in PostgreSQL.