How to subtract count of two columns in MYSQL-CodePudding

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

CodePudding user response：

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';