SELECT
s.customer,
s.customer_name,
s.Sub_Customer_Group,
s.Sub_customer_name,
s.FSRTerritory_Name,
k.kit_nokit,
SUM(CASE WHEN YEAR(s.period) IN (2021, 2022) THEN s.sales * 0.7726 ELSE 0 END) AS sales_2021_2022,
SUM(CASE WHEN YEAR(s.period) = 2023 THEN s.sales * 0.7726 ELSE 0 END) AS sales_2023,
SUM(s.sales * 0.7726) AS Sales_SinceLaunch,
SUM(s.volume),
SUM(CASE WHEN s.sales_unit = 'MP-5 Pack' AND s.Dx_actual IS NOT NULL THEN s.Dx_actual ELSE 0 END) AS DX_Actual_Sum,
CASE WHEN SUM(s.volume) >= 18 THEN 'Activated' ELSE 'Not Activated' END AS Activated,
CASE WHEN SUM(s.volume) / 6 >= ABS(TIMESTAMPDIFF(MONTH, MIN(s.Period), '2023-05-01')) + 1 AND SUM(s.volume) >= 12 AND (s.sales_unit = 'MP-30 Pack' OR (s.sales_unit = 'each' AND s.sales_unit != 'MP-5 Pack'))
THEN 'Productive'
ELSE 'Not Productive'
END AS Productive_Status
FROM
score s
LEFT JOIN
kit k ON s.customer = k.Customer
WHERE
(s.sales_unit = 'MP-30 Pack' OR s.sales_unit = 'each')
GROUP BY
s.customer, s.customer_name, s.Sub_Customer_Group, s.Sub_customer_name, s.FSRTerritory_Name, k.kit_nokit;
I want to use where (s.sales_unit = 'MP-30 Pack' OR s.sales_unit = 'each') for all expect Dx_Actual. For Dx_Actual, my where condition will be s.sales_unit = 'MP-5'