Mysql – count distinct column without null value in MySQL 5.7

MySQL

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?

Best Answer

MySQL works like you expected and it should return 1.

create table t (a int, b int);
insert into t (a, b) values (1, null), (2, 1);
select * from t;
select count(distinct b) from t;

dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=4fd6a3744a042c376880f030f4d12101

Related Question