Generating a table of subtraction of two colum count

Question

Hello i want to make a summary table where the filter is the segment and date The columns will be the subtraction of count of two column in same table so far i have come up to this

select
    case
        when SEGMENT='champion'THEN SUM(SEGMENTED_DATE = '2022-10-14')-SUM(SEGMENTED_DATE='2022-10-07')




    END as total_change_champion,

    case
        when SEGMENT='Hibernating'THEN SUM(SEGMENTED_DATE = '2022-10-14')-SUM(SEGMENTED_DATE='2022-10-07')

    END as  total_change_hibernate

from weekly_customer_RFM_TABLE;

This query is giving me null value for columns

select

    SUM(SEGMENTED_DATE = '2022-10-14')-
    SUM(SEGMENTED_DATE='2022-10-07')as total_changes_in_14th
From weekly_customer_RFM_TABLE
where
SEGMENT ='Hibernating'

This query is actually giving me the result. but when I am going to make a consolidated table using different segment subtraction i am getting null value in my result table.Kindly help me out

my sample data

which is i want to substract totalcount of 2022/10/14 - 2022/10/07 for each segment

image is not loading unfortunately

Answer 1

You must include the CASE expressions inside the aggregate functions:

SELECT COUNT(CASE WHEN SEGMENT = 'champion' AND SEGMENTED_DATE = '2022-10-14' THEN 1 END) -
       COUNT(CASE WHEN SEGMENT = 'champion' AND SEGMENTED_DATE = '2022-10-07' THEN 1 END) AS total_change_champion,
       COUNT(CASE WHEN SEGMENT = 'Hibernating' AND SEGMENTED_DATE = '2022-10-14' THEN 1 END) -
       COUNT(CASE WHEN SEGMENT = 'Hibernating' AND SEGMENTED_DATE='2022-10-07' THEN 1 END) AS total_change_hibernate
FROM weekly_customer_RFM_TABLE;

For MySql this can be simplified to:

SELECT SUM(SEGMENT = 'champion' AND SEGMENTED_DATE = '2022-10-14')-
       SUM(SEGMENT = 'champion' AND SEGMENTED_DATE = '2022-10-07') AS total_change_champion,
       SUM(SEGMENT = 'Hibernating' AND SEGMENTED_DATE = '2022-10-14')-
       SUM(SEGMENT = 'Hibernating' AND SEGMENTED_DATE='2022-10-07') AS total_change_hibernate
FROM weekly_customer_RFM_TABLE;

You may also use a WHERE clause to filter the table for only the SEGMENTs and SEGMENTED_DATEs that you want:

WHERE SEGMENT IN ('champion', 'Hibernating') AND SEGMENTED_DATE IN ('2022-10-07', '2022-10-14')