I am having trouble running this conditional count:
COUNT(CASE WHEN purchase.status = 'complete' THEN purchase.purchase_id ELSE 0 END) AS num_completed_purchases,
SUM(CASE WHEN purchase.status = 'complete' THEN purchase.price ELSE 0 END) AS total_sales_revenue
LEFT JOIN purchases ON products.product_id = purchases.product_id
GROUP BY products.product_id
The SUM() is working fine but the COUNT() doesn't seem to give me the desired result. If a product has 2 associated records in the purchases table, 1 'complete' and 1 not, then num_completed_purchases should equal 1. However as long as the status = 'complete' condition is met in one row then all rows for that product are counted (num_completed_purchases is returned as 2). However only 'completed' purchases are added to the SUM() of total_sales_revenue.
What am I doing wrong? Why does it COUNT() all or none?
Thanks in advance.