how to distinct not contain null in MySQL, if I have a dataset like this:
a b
1 null
2 1
I want to using sql like this:
select count(distinct b)
fromt table
the expect result may output 2, but I want the count distinct result not contain null. I could not using filter in the where clause because I should using other column to sum as result, if I filtered result first, the result is not correct. My real sql like this:
select
sum(transin_num) as transinNum,
case when count(distinct transin_user_num) -1 < 0 then 0 else count(distinct transin_user_num) -1 end as
transinUserNum,
sum(transin_count) as transinCount
from (
select
case when consume_item in('PLATFORM_IN') then ABS(consume_num) else 0 end as transin_num,
case when consume_item in('PLATFORM_IN') then user_id else -1 end as transin_user_num,
case when consume_item in('PLATFORM_IN') then 1 else 0 end as transin_count
from wallet_consume_record
where created_time < 1595951999999
and created_time > 1595865600000
and tenant_id = 84
) a
I want to calculate transin_user_num without the -1 value.Is it possible to do like this in MySQL 5.7?
COUNT(b)says how many rows haveb IS NOT NULL.