MySQL Conditional Count

[jblangworthy]

I am having trouble running this conditional count:

SELECT
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
FROM products
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.

[kleineme]

Hi,

COUNT(*) counts all records, COUNT(column) counts all records where column is not NULL. So you may try this:

COUNT(CASE WHEN purchase.status = 'complete' THEN 1 ELSE NULL END) AS num_completed_purchases,

[r937]

kleineme's solution works --

COUNT(CASE WHEN purchase.status = 'complete'
THEN 1 ELSE NULL END)

so does this --

COUNT(CASE WHEN purchase.status = 'complete'
THEN 937 ELSE NULL END)

and so does this (which is what i think you meant in your original query) --

SUM(CASE WHEN purchase.status = 'complete'
THEN 1 ELSE 0 END)

[jblangworthy]

Thanks Both for your replies. The SUM (CASE WHEN condition THEN 1 ELSE 0 END) works well, as does the COUNT (CASE WHEN condition THEN 1 ELSE NULL END). I didn't realise that COUNT ignores NULL rows but that does make sense. I was too busy counting like a human.