PostgreSQL: Unpredictable results with GROUP BY when using CASE in aggregated columns
I'm running into an scenario with a query in PostgreSQL 14 that uses a `CASE` statement within an aggregate function... The intention is to count the number of orders per customer based on their status, but I'm getting unexpected results. Here's the query I'm using: ```sql SELECT customer_id, COUNT(CASE WHEN order_status = 'completed' THEN 1 END) AS completed_orders, COUNT(CASE WHEN order_status = 'pending' THEN 1 END) AS pending_orders FROM orders GROUP BY customer_id; ``` The question arises when I have customers with no completed or pending orders. Instead of returning a count of 0 for these customers, they aren't appearing in the results at all. I've tried changing the `GROUP BY` clause and even using `LEFT JOIN` with a subquery to include customers, but it hasn’t worked as expected. I've also experimented with using `COALESCE` around the `COUNT` functions to force a return of 0, but it still omits customers without any orders: ```sql SELECT customer_id, COALESCE(COUNT(CASE WHEN order_status = 'completed' THEN 1 END), 0) AS completed_orders, COALESCE(COUNT(CASE WHEN order_status = 'pending' THEN 1 END), 0) AS pending_orders FROM orders GROUP BY customer_id; ``` Is there a way to modify this query so that customers with no orders still show up in the results with a count of 0 for both completed and pending orders? I'm considering using a different approach, like a CTE or subquery to first get all customers, but I’d prefer to keep it simple if possible. Any insights would be greatly appreciated! The project is a application built with Sql.