Comparing a column value to a constant from CASE WHEN fails

MySQL 8.0.40

The following code works exactly as intended. I create two CTE's, each returning two columns. I then compare the value of the Jan 2022 column to the Jan 2021 column. If Jan 2022 is greater, create a new column named "2022_is_greater" and fill with a 1, else 0.

However, if I try to filter on the newly created "2022_is_greater" column, it doesn't work.

ON
 Pr_2021 = Pr_2022
WHERE
 '2022_is_greater' = 1;

appended to the end results in zero rows returned (should be 18).

For grins I also tried:

ON
 Pr_2021 = Pr_2022
WHERE
 '2022_is_greater' > x;

where 'x' is any integer, 0 or larger, it returns all 18 rows. (As it turns out, in every row the Jan 2022 value is larger). But this should return zero rows for any value of x greater than 0.

I am also getting this when I add the comparison via the WHERE clause:

Truncated incorrect DOUBLE value: '2022_is_greater'

I looked it up but I don't understand why I am getting this. I am simply checking if the value in the column is 1 or not. I am not performing a string comparison.

Correctly functioning full query without the WHERE clause at the very end:

WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022;

Solution

You could make a further CTE and use it as base

WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
), S_UM  as (
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022
 FROM
 S_UM
 WHERE 2022_is_greater = 1;

Or you can use HAVING

WITH Jan2021 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2021,
     pr.Product as Pr_2021
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2021-01-01' AND '2021-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2021 DESC
),
Jan2022 AS (
    SELECT
     SUM(s.Amount) as SUM_Jan_2022,
     pr.Product as Pr_2022
    FROM
     sales AS s
    INNER JOIN
     products AS pr
    ON
     pr.PID = s.PID 
      AND DATE(s.SaleDate) BETWEEN '2022-01-01' AND '2022-01-31' AND pr.Category != 'Other'
    GROUP BY
     pr.Product
    ORDER BY
     SUM_Jan_2022 DESC
)
SELECT
 SUM_Jan_2021,
 SUM_Jan_2022,
 Pr_2021,
 Pr_2022,
 CASE WHEN SUM_Jan_2022 > SUM_Jan_2021 THEN 1
      ELSE 0
 END AS "2022_is_greater"
FROM
 Jan2021 AS J21
INNER JOIN
 Jan2022 AS J22
ON
 Pr_2021 = Pr_2022
 HAVING  2022_is_greater = 1;