How to exclude 0 from count()? in sql?

February 6, 2022

I have a code as below where I want to count number of first purchases for a given period of time. I have a column in my sales table where if the buyer is not a first time buyer, then ‘is_first_purchase = 0"

For example:
buyer_id = 456391 is already an existing buyer who made purchases on 2 different dates.
Hence is_first_purchase column will show as 0 as per below.

If i do a count() on is_first_purchase for this buyer_id = 456391 then it should return 0 instead of 2.

My query as follows:

    with first_purchases as 
(select *,
case when is_first_purchase = 1 then 'Yes'  else 'No' end as first_purchase
from sales)
    
    select 
count(case when first_purchase = 'Yes' then 1 else 0 end) as no_of_first_purchases 
from first_purchases 
where buyer_id = 456391 
and date_id between '2021-02-01' and '2021-03-01' 
order by 1 desc;

It returned the below which is not an intended output

Appreciate if someone can help explain how to exclude is_first_purchase = 0 from the count, thanks.

>Solution :

Because COUNT function count when the value isn’t NULL (include 0), if you don’t want to count, need to let CASE WHEN return NULL

There are two ways you can count as your expectation, one is SUM other is COUNT but remove the part of else 0

SUM(case when first_purchase = 'Yes' then 1 else 0 end) as no_of_first_purchases 

COUNT(case when first_purchase = 'Yes' then 1 end) as no_of_first_purchases

From your question, I would combine CTE and main query as below

select 
    COUNT(case when is_first_purchase = 1 then 1 end) as no_of_first_purchases 
from sales 
where buyer_id = 456391 
and date_id between '2021-02-01' and '2021-03-01' 
order by 1 desc;