0

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?

2
  • You may want to make it possible to recreate the situation? Commented Jul 28, 2020 at 16:31
  • COUNT(b) says how many rows have b IS NOT NULL. Commented Jul 29, 2020 at 3:51

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.