How to subtract count of two columns in MYSQL

November 4, 2022

I am wanting to get the difference of count value of two columns in same table so far i come up with this code

select (select count( user_id)
        from weekly_customer_RFM_TABLE
        where SEGMENT = 'champion'
          and SEGMENTED_DATE = '2022-10-14'
            -
                               (select
                                    count( user_id)
                                    from weekly_customer_RFM_TABLE
                                    where SEGMENT = 'champion'
                                    and SEGMENTED_DATE = '2022-10-07')) as total_changes

i am getting result 0 here but the original result will be 45
I cannot figure out what i am missing here. Please help me to solve the problem. Those data are in same table but filter by segment and dates

>Solution :

We can use conditional aggregation here with a single pass over the weekly_customer_RFM_TABLE table:

SELECT SUM(SEGMENTED_DATE = '2022-10-14') -
       SUM(SEGMENTED_DATE = '2022-10-07') AS total_changes
FROM weekly_customer_RFM_TABLE
WHERE SEGMENT = 'champion';