add an unknown column in where clause

August 6, 2024

I need to filter a column using a WHERE condition but I get this error:

ERROR 1054 (42S22): Unknown column 'GOALS_MIN' in 'where clause'

This is my structure:

+-----------------------+-------+-----------+-----------+
| TEAM                  | GOALS | GOALS_MAX | GOALS_MIN |
+-----------------------+-------+-----------+-----------+

and this is my actual query:

SET @TOTAL := (select count(id) from matches);
SELECT
  TEAM,
  sum(RESULT) as GOALS,
  max(RESULT) as GOALS_MAX,
  min(RESULT) as GOALS_MIN
FROM
  (SELECT HOME as TEAM,
    CASE WHEN RESULT_HOME > RESULT_VISIT THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_HOME = RESULT_VISIT THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_HOME < RESULT_VISIT THEN 1 ELSE 0 END as LOSS, RESULT_HOME as RESULT
  FROM matches
  UNION ALL
 SELECT VISIT as TEAM,
    CASE WHEN RESULT_VISIT > RESULT_HOME THEN 1 ELSE 0 END as WIN,
    CASE WHEN RESULT_VISIT = RESULT_HOME THEN 1 ELSE 0 END as DRAW,
    CASE WHEN RESULT_VISIT < RESULT_HOME THEN 1 ELSE 0 END as LOSS, RESULT_VISIT as RESULT
  FROM matches
) as t 
WHERE TEAM LIKE '%TEAM1%' OR TEAM LIKE '%TEAM1%' AND GOALS_MIN >= '2'
GROUP BY TEAM
ORDER BY WIN DESC;

How can I filter via column GOALS_MIN using the last AND (GOALS_MIN >= ‘2’)

Thanks in advance.

>Solution :

that beacuse the GOALS_MIN not exist in table t , and GOALS_MIN value is clac in outer query , try this :
SET @TOTAL := (SELECT COUNT(id) FROM matches);

SELECT
TEAM,
SUM(RESULT) AS GOALS,
MAX(RESULT) AS GOALS_MAX,
MIN(RESULT) AS GOALS_MIN
FROM
(
SELECT HOME AS TEAM,
RESULT_HOME AS RESULT
FROM matches
UNION ALL
SELECT VISIT AS TEAM,
RESULT_VISIT AS RESULT
FROM matches
) AS t
WHERE TEAM LIKE ‘%TEAM1%’
GROUP BY TEAM
HAVING MIN(RESULT) >= 2
ORDER BY WIN DESC;